Copyright: (C) 1992-1998 Visio Corporation
Citation: 'Visio Enterprise 5.0 Online Help, (C) 1992-1998 Visio Corporation


Understanding the database modeling process

 Related Topics

Visio® Enterprise provides powerful database modeling tools you can use to create logical models for relational and object-relational databases. A well-designed logical database model can help you ensure that any information system you design is accurate, complete, and efficient by showing in a visual format the relationships between the tables of information stored in the database. With Visio Enterprise, you can reverse engineer a model from an existing database, update a model based on changes in the database, generate a new database from the model, and update or forward engineer an existing database based on changes in the model.

This set of topics introduces the Visio Enterprise database modeling tools and provides an overview of the database modeling process.

Topics in this section

About the database modeling process in Visio Enterprise

Creating a database model

Shapes and notation

Next steps


About the database modeling process in Visio Enterprise

 How To	 Related Topics

As an experienced database designer, you may work with other Visio® products. If so, many of the tools in Visio Enterprise will be familiar. Other tools, such as the Types window, Tables & Views window, Code window, and Output window, may be new to you. This section introduces the basic process for modeling databases with Visio Enterprise and the user interface elements you’ll use.

In addition to providing tools for creating database model diagrams, Visio Enterprise delivers functionality for connecting to a wide range of database management systems (DBMSs). As part of this functionality, Visio Enterprise offers workgroup-related support using Microsoft Repository.

When you model a database in Visio Enterprise using the Database Model template, your process may include some or all of the steps illustrated in the following figure.

Sample process for creating a database model

1	Creating Whether you choose to reverse engineer an existing database or start a model from scratch, you begin in the Database Model template and add tables and relationships to the diagram to symbolize your database.

2	Refining Once you’ve started a database model, the real work of refining it begins. You can modify tables, columns, relationships, data types, and views; set extended properties; define indexes; and make many other refinements.

3	Generating a database schema When you’re satisfied with your model, you can use the Generate Wizard to create a new database, a DDL (Data Definition Language) script that you can review and that your DBMS can use to create a new database, or both.

4	Updating If you reverse engineered an existing database, you can use the Update Model Wizard to update your model based on changes to the physical database. In addition, you can use the Update Database Wizard to update or forward engineer the physical database based on changes you’ve made in your model.

5	Creating reports Use the Report Wizard anytime in the process to generate reports that document your design or communicate information about the database to a client.


Working in a Visio Enterprise diagram

 How To	 Related Topics

The Visio® Enterprise drawing page contains the essential visual expression of a database model. The shapes in the diagram show the elements you want the database to store information about and make the relationships among them visible. Other information about your database, such as code for stored procedures, can also be included in the model, though it may not be visible in the diagram.

When you save your database model, Visio Enterprise saves it as a Visio drawing(.vsd) file.

The Visio Enterprise user interface

A	The Database menu contains commands for creating, refining, updating, and generating the logical model and any associated databases.

B	Stencils contain shapes you can drag and drop on the drawing page to add new elements to the model. The Database Model template contains two stencils: Entity Relationship and Object Relational.

C	The drawing page contains the shapes that represent the database model.


Using the Database menu

 How To	 Related Topics

When you start Visio® Enterprise and open the Database Model template, the Database menu appears with commands that are unique to working in the database modeling solution. You also can right-click any model component to access component-specific commands.

Update Opens the Update Database Wizard, so you can update an existing database to reflect your model.

Generate Opens the Generate Wizard, so you can generate a new database directly or with a DDL script containing DBMS-specific commands.

Reverse Engineer Opens the Reverse Engineer Wizard, so you can extract a model from an existing database.

Model > Check Validates your model and displays any errors and warnings in the Output window..

Model > Update Opens the Update Model Wizard, so you can update your model from a database.

Report Opens the Report Wizard, so you can choose one of three report types to communicate information about your model.

View Provides commands to open the Code window, Tables & Views window, Types window, Output window, and Verbalizer window..

Code > Synchronize Keeps code that is saved externally in a mirror file in sync with the code in the model.

Code > Transfer Transfers code for triggers, procedures, or check clauses into a DBMS-specific format.

Import/Export Provides options to import existing database models from PLATINUM ERwin, Visio® Professional, and VisioModeler™ and to export models to ERwin and VisioModeler.

Drivers Sets default drivers and mapping preferences, and defines options for generating DDL scripts.

User Defined Types Opens the User Defined Types dialog box, in which you can create user-defined data types.

Document Options Opens the Database Document Options dialog box, in which you can choose the default notation and display options for the current database model.

Modeling Preferences Opens the Database Modeling Preferences dialog box, in which you can set default database modeling behavior that persists between Visio Enterprise sessions.


Working with Microsoft Repository

 How To	 Related Topics

Through its Database Model solution, Visio® Enterprise supports sharing and exchange of database models. When you install Visio Enterprise, you also install and register all the components you need to store models in and retrieve them from Microsoft Repository.

Microsoft Repository is a storage mechanism that consists of ActiveX interfaces that developers can use to define information models, a Repository Engine that is the underlying storage mechanism for the models, and either a SQL Server or Microsoft Jet database in which the schema is stored. Microsoft Repository uses the Database Information Model (DBM) as a format for storing a database’s data requirements independent of the physical implementation of the database. This common format enables you to easily share and update components in a team setting. When a database schema is stored as an information model, developers can access and use the components regardless of which tool the components were created in or on which platform they run.

To store your Visio Enterprise database model in Microsoft Repository, you establish a connection with the DBM Repository driver Visio Enterprise provides. The DBM Repository driver extracts the information from the model and converts it to a format that Microsoft Repository can read. The DBM Repository driver communicates with the Repository Engine, which manages the model data in Microsoft Repository. Your database model is then stored in a Repository database as a set of objects and the relationships between them.

NOTE  Although the Repository database is stored in either a SQL Server or Microsoft Jet database , you can extract your database model from Microsoft Repository and specify any Visio Enterprise–supported database platform for implementation. The storage mechanism for Microsoft Repository is independent of the implementation of your database model.

The following figure illustrates the Repository architecture. Visio Enterprise supports Microsoft Repository version 2.0.

The Repository architecture

Visio Enterprise Database Model Model your database schema in Visio Enterprise.

Database Information Model Create a connection with the Microsoft Repository Database Information Model to extract the objects and relationships in a format Microsoft Repository can read.

Repository Engine Communicates with the DBM and manages the data in Microsoft Repository. It also provides the functions for storing the database model in the Repository database.

Repository Database A SQL Server or Microsoft Jet database in which the database model structure is stored.


 Creating a database model

 How To	 Related Topics

When you want to create a database model in Visio® Enterprise, you begin by opening the Database Model template. The Database Model template contains tools and commands to create new database models, and to connect to a DBMS to generate new or update existing databases.

The Database Model template opens the Entity Relationship stencil , the Object Relational stencil, and a blank drawing page. Once you’ve opened a template, you can take one of three approaches to start a database model in Visio Enterprise.

•	Create a new model from scratch. Visio Enterprise provides the tools and controls you need to plan and design complex databases.

•	Use the Reverse Engineer Wizard to extract a model from an existing physical database. Reverse engineering is the most widely used method for creating a database model, as it allows you to improve, enhance, and extend an organization’s existing databases. You can choose to extract code stored as part of the database when you reverse engineer a database, and you can review and edit the code as you refine your model.

•	Import an existing model from PLATINUM ERwin, Visio Professional, or VisioModeler. Importing an existing database model also saves time by building on existing information.

The following figure illustrates the three methods you can use to start a database model in Visio Enterprise.


Shapes and notation

 How To	 Related Topics

The stencils in the Visio® Enterprise Database Model template include SmartShapes® symbols, which are programmed to behave in very specific ways in accordance with the chosen modeling method. Shapes in the Entity Relationship stencil can be used to model relational databases. Shapes in the Object Relational stencil can be used to model either relational or object-relational databases.

Choose to use shapes from the Entity Relationship or Object Relational stencil based on the type of DBMS you will use to create the physical database.

•	Entities represent the tables in which you store data. Object-relational tables can include complex types, which act like nested records.

•	Relationship connector shapes show the parent-child relationships between tables. A child table contains information derived from a parent table.

•	Categories show the relationship of multiple child tables to a parent table.

•	Views contain columns derived from other tables and represent alternative combinations of information stored in the database.

•	Parent To Category connector shapes link a parent table to a Category shape.

•	Category To Child connector shapes link child tables to a Category shape, and establish foreign key relationships with the parent table.

•	Table Inheritance connector shapes set child tables to inherit or derive the attributes of a parent table automatically.

•	Type Inheritance connector shapes set a type to inherit or derive the attributes of a parent type automatically.

•	Composite Type shapes allow you to embed subtables in an object-relational database model by defining a type with multiple columns and assigning it as the data type for a column in a table.


Customizing a model's on-screen appearance
Customizing a model’s on-screen appearance

 How To	 Related Topics

The way the shapes in a model appear on the screen is determined by the notation type you select for the Symbol Set and other options you select in the Database Document Options dialog box. Visio® Enterprise supports IDEF1X and Relational notation. You can also choose to display relationships for either notation using Crow’s Foot symbols.

In addition to choosing the notation your database model uses, you also determine the way Visio Enterprise draws relationships and the amount of detail shown in your model. For example, you can choose whether to display referential integrity, all or only selected columns in a table, whether data types are visible, and many other options.

Changing notation options changes the appearance of a model on the drawing page, but does not affect the information in the model. Other options determine the level of detail visible in the diagram.

IDEF1X notation with referential integrity notation visible

Relational notation with data types visible

If you right-click a table, view, or type, then choose Override Document Options, you can customize display options for that element.


Using right-click shortcut menus

 Related Topics

You can right-click any component of your model to display a shortcut menu of additional commands for refining it. For example, you can right-click a table to access the Edit Columns command, which you can use to edit a column’s properties. Or, you can right-click a relationship to access the Relationship Properties dialog box, where you can edit foreign key definitions and change referential integrity settings.


Using the Code window, Tables & Views window, and Types window

 Related Topics

You can further refine a database model using the Visio® Enterprise Code Window, Tables & Views window, and Types window. To open any of these windows, choose Database > View, then choose the window you want to display. Following are descriptions of each window:

Code window Lists all code for a particular database platform that is associated with your model, including code extracted from a database during reverse engineering. The Code window also provides access to the Code Editor, where you can revise existing code or write new code.

Tables & Views window Appears automatically when you use the Reverse Engineer Wizard, and lists the names of tables and views extracted from the database. When you drag elements from the Tables & Views window and drop them into your diagram, they appear in your model, where you can view them along with any associated relationships.

Types window Contains the built-in physical data types for your target database, portable data types you created using the User Defined Data Types dialog box, and composite types you’ve defined. Once you are familiar with the physical data type requirements of your DBMS, you can assign data types to specific columns in the model.


Validating a model

 How To	 Related Topics

As you refine your database, you can validate it periodically to ensure that it is free of errors. Visio® Enterprise performs two types of validation.

•	Logical validation checks the database model to ensure that it conforms to generally accepted database design standards. For example, circular table relationships are identified when you validate a model, along with duplicate column names.

•	Physical validation checks the model to be sure it is valid for the target DBMS. For example, Microsoft SQL Server allows only one column in a table to have an identity property set. A table containing more than one column with this physical attribute will not generate an error during logical validation, but will fail physical validation.

Validating your model as you refine it can help ensure that your database is as efficient as possible, and can streamline the process of using the Generate, Update Model, and Update Database Wizards.


Next steps

 Related Topics

To explore the Visio® Enterprise database modeling solution, see the topics below.

If you want to	See
Create a new logical database model	Creating database models
Refine a logical model	Refining database models
Reverse engineer code	Reverse engineering database code
Connect to a database or understand how Visio Enterprise uses drivers and data sources	Connecting to a DBMS
Generate a new database	Generating a database schema
Update your logical model with changes from the physical database	Updating models and databases
Update your database based on your logical model	Updating models and databases
Create a report	Creating reports


Creating database models

 Related Topics

Visio® Enterprise supports the goals of data modeling: to capture real-world information about an application domain accurately, and in a way that is meaningful and understandable to both the user and the database designer. With the Database Model solution in Visio Enterprise, you can design a database schema and re-engineer existing databases by creating a model. A model is a Visio Enterprise diagram in which you represent the logical structure of a database using shapes, which you can then modify to refine the database schema. Ultimately you can forward engineer your model to an existing database or use it to generate a new database schema.

This set of topics describes how to start working in the Database Model solution to create a new database model from scratch or from an existing database or model created in another program.

Topics in this section

Creating a new database model

Creating a database model from scratch

Reverse engineering an existing database

Importing a data model

Customizing your database model diagram

Next steps


Creating a new database model

 How To	 Related Topics

Whether you start from scratch or from an existing database or model, you begin a new database model in Visio® Enterprise by opening the Database Model solution. This solution includes stencils of shapes that represent the components of relational and object-relational data models. In addition, opening this solution adds the Database menu to the Visio Enterprise toolbar, which contains the commands you use to work with database model diagrams.


To open the Database Model solution
To open the Database Model solution:

•	Choose File > New > Database > Database Model.

Visio® Enterprise creates a new, empty drawing page and opens the Entity Relationship and Object Relational stencils.

After you open the Database Model solution, you can start to create your database model in three ways. You can

•	Drop the Entity Relationship or Object Relational shapes on the drawing page to construct a model from scratch, which you can later use to generate a new database schema.

•	Reverse engineer all or part of the schema of an existing database, which automatically creates a model on the drawing page.

•	Import a data model created with Visio® Professional, VisioModeler™ (formerly InfoModeler®), or PLATINUM ERwin.

  Overview


Representing relational and object-relational databases

 How To	 Related Topics

In the Database Model solution, you can create models that represent either relational or object-relational databases. The Database Model solution opens with the Entity Relationship stencil for creating relational database models, and the Object Relational stencil for creating object-relational database models with extended functionality such as composite data types, type inheritance, and table inheritance. Each type of model in Visio® Enterprise has its own set of tools and professional reports that allow you to focus on your task of clearly and accurately describing the information requirements of users.

By working with the shapes from one stencil, you can choose the modeling method you want to use to build your database model diagram. Relational and object-relational notations share many common elements.

•	Tables or entities, which you can create using the Entity shape.

•	Columns or attributes, which you can create by right-clicking an Entity shape, then choosing Edit Columns from the shortcut menu. Notation for primary keys, foreign keys, alternate keys, and indexes is supported.

•	Relationships, which you can create using the Relationship shape, which includes support for referential integrity and IDEF1X cardinality constraints.

You use the shapes on the Entity Relationship or Object Relational stencil to represent tables, columns, and other components as they appear in a database.

For details about these and other ways of customizing a database model, see Customizing your database model diagram.


Setting display options for a database model

 How To	 Related Topics

With either stencil, you can choose the notation symbolized in the shapes.

•	IDEF1X Describes a data model as entities with attributes that participate in relationships. Entities are depicted as rectangles with the entity’s name on top and its attributes inside. A relationship between entities is portrayed by a 1-D connector shape.

•	Relational Uses tables, columns, and keys to describe your data model. Tables are represented as rectangles with the table’s name across the top. Columns of the table are listed vertically underneath the name of the table. A foreign key relationship is shown by a 1-D connector shape.

Visio® Enterprise gives you the flexibility to switch between modeling notations easily without losing any information in the model.

As you work, you can switch easily between notations—helpful if you are collaborating with a colleague who prefers a different notation. In addition, for either notation, you can further specify whether to show relationships using Crow’s Foot notation, a variation of connector style that indicates relationships between entity shapes.

You can also specify the level of detail displayed in a database model diagram. By showing and hiding levels of detail, you can create a more detailed or condensed view of your model. For example, you can display just the elements you want, such as the table name, primary keys, foreign keys, and relationships, or you can choose to look only at primary keys and foreign keys and not to display data types.


To customize how a database model appears
To customize how a database model appears:

1	Open the Visio® Enterprise Database Model file that represents your model.

2	Choose Database > Document Options.

3	On the tabs in the Database Document Options dialog box, do the following:

General tab Choose the graphical notation set and name options you want to display.

Table tab Choose column, table, view, and type display attributes, primary key order, and data type display, and whether to display IDEF1X optionality.

Relationship tab Choose whether to display relationships. If you choose to display relationships, select whether to represent them with Crow’s Foot notation, whether to display cardinality and referential integrity, and how to display the relationship verb phrase and name.

Optional To keep the settings you choose as the default settings, click the Defaults button, then choose Set As. To return the settings to the most recently saved defaults, choose Restore. To return to the Visio Enterprise defaults, choose Restore Original.

4	Click OK.

TIP  For an individual shape in your model, you can override the notation preferences set for the model. For example, if your client has a specialized notation for tables, you can override the default settings of the model to show a table with different notation. To do this, right-click the shape you want to change, choose Override Document Options from the shortcut menu, then manually set the properties you want.

  Overview


Creating a database model from scratch

 How To	 Related Topics

When you design a model from scratch, you create a new model diagram with an empty drawing page, and then use the shapes on the Entity Relationship or Object Relational stencil to design a database schema.

In general, you follow these steps when constructing a data model.

1	Draw a table (or entity).

2	Insert columns (or attributes).

3	Draw the relationships between the tables (or entities).

4	Define and set the properties for each component of your data model.

Once you have completed these basic steps, you refine your design, repeating the general procedure until you’re ready to use your design to generate a schema for your target DBMS (database management system).


To create a new database model diagram from scratch
To create a new database model diagram from scratch:

1	Choose File > New > Database > Database Model.

Visio® Enterprise creates a new, empty drawing page and opens the Entity Relationship and Object Relational stencils.

2	Choose Database > Document Options, then on the General tab, choose the notation you want to use and other table and relationship options, then click OK.

3	Drag an Entity shape from a stencil and drop it onto the drawing page.

4	Add attributes or columns.

5	Define the table and its properties, such as primary ID and indexes.

6	Add Entity shapes to the drawing page and define attributes or columns for the other entities or tables you want in the model.

7	Add a relationship.

8	Edit the properties of the relationship.

9	Define relationships between the remaining Entity shapes on the drawing page.

You can add views, categories, code, and further refinements to your model.

  Overview


Reverse engineering an existing database

 How To	 Related Topics

Probably the simplest and most powerful way to create a new database model in Visio® Enterprise is to reverse engineer an existing database. The Reverse Engineer Wizard in the Database Model solution automates the process of extracting the schema from a database that you specify, including Microsoft Repository. You use the wizard to specify the platform-specific database objects that you want to reverse engineer: tables, primary keys, indexes, foreign keys, triggers, check clauses, views, and stored procedures. Then the wizard extracts, analyzes, and validates the schema into a database model diagram that you can review and modify.

The Reverse Engineer Wizard steps you through the process of connecting to the target DBMS that you want to reverse engineer. To create a connection, you can use the wizard to specify an installed Visio Enterprise driver and the 32-bit ODBC (Open Database Connectivity) data source. However, for best results, set your default driver to the target database you want to reverse engineer before running the wizard. The information that the wizard can extract depends upon the capabilities of the DBMS. If your DBMS makes some of the schema definition available, Visio Enterprise makes those options available in the Reverse Engineer Wizard and dims the other options.

You can choose the schema information to extract in the Reverse Engineer Wizard.

The wizard notifies you of its progress in the Output window. When the extraction process is complete, the wizard adds the extracted structures to the currently open model diagram and displays them in the Tables & Views window.


Connecting to a data source to reverse engineer

 How To	 Related Topics

The first time you extract information from a database, you must specify how to locate and communicate with that database by associating the appropriate drivers. Visio® Enterprise uses one of its own database drivers, in conjunction with a 32-bit ODBC driver, to connect to and extract information from a database.

You can set up the drivers before you run the Reverse Engineer Wizard by choosing Database > Drivers. Or you can use screens in the wizard to perform this step. Either way, you must

•	Select an installed Visio Enterprise database driver.

•	Associate a 32-bit ODBC driver with an installed Visio Enterprise database driver.

•	Select or create a 32-bit ODBC data source. For details about creating a data source, see the documentation for the Microsoft 32-bit ODBC Data Source Administrator.

Once you have associated drivers, either in the Database Drivers dialog box or the Reverse Engineer Wizard, you do not need to repeat this step unless you want to change an association you have made.

The drivers you installed with Visio Enterprise appear in the Installed Visio Drivers box in the Reverse Engineer Wizard. After you choose the Visio Enterprise driver you want to use, you can associate it with the 32-bit ODBC driver for your target database if you have not already done so. The 32-bit ODBC driver that you choose determines the data sources you see in the list of available data sources. If you do not create an association, Visio Enterprise lists all the data sources on your system. The association tells Visio Enterprise which 32-bit ODBC driver to use if you have more than one installed on your system.

When you’re starting a new model, it’s probably easier to set up the driver associations before running the Reverse Engineer Wizard. For details, see Connecting to a DBMS.


Extracting code with the Reverse Engineer Wizard

 How To	 Related Topics

A database can include SQL code in the form of stored procedures, triggers, check clauses, functions, and so on. This code is preserved when you reverse engineer a database, so that you can work with code components of your model in Visio® Enterprise. When you reverse engineer a database that contains code, the Reverse Engineer wizard copies the SQL code as text and then adds the text to the model as one of the following types of code:

•	Stored procedure

•	Trigger

•	Function

•	Check clause

•	View definition (but not the join criteria for views)

•	Raw DDL

In the Reverse Engineer Wizard, you can choose the types of code to extract with the database schema.

After you reverse engineer a database that contains code, Visio Enterprise lists each code component in the Code window in the order it was extracted. To display the Code window, choose Database > View > Code Selector.

The Code window displays the version of code specific to the platform from which it was reverse engineered. However, you can transfer code to a different platform to store multiple versions of code with your model.


Stored procedure

User-defined functions and other procedures that can be invoked with a CALL statement and are stored in an executable format in the database. Designated in the Code window by [image].


Function

Procedural code that returns a value. Designated in the Code window by [image].


Trigger

Code associated with specific database tables that is invoked automatically whenever rows of the associated table are inserted, updated, or deleted. Designated in the Code window by [image].


Check clause

Code applied to a table or column that enforces constraints. Designated in the Code window by [image].


View definition

Code usually containing SELECT statements that assigns the results of a query to a named, table-like structure called a view. Designated in the Code window by [image].


Raw DDL

Data Definition Language code; that is, other SQL code that does not fall into one of other categories. For example, synonyms or package constructs in an Oracle database. Designated in the Code window by [image].


Extracting a schema using the Reverse Engineer Wizard

 How To	 Related Topics

You can extract all or part of the schema of an existing database into a new or existing model that you can then refine. The Reverse Engineer Wizard connects to your target DBMS, extracts the schema information you choose, and adds it to the currently open model diagram.

NOTE  For best results, before you run the Reverse Engineer Wizard, choose Database > Drivers to set the default driver to the target back end. This step ensures that the wizard will map the native data types correctly and that all the code extracted by the wizard will be correctly displayed in the Code window.


To extract a database schema into a model diagram
To extract a database schema into a model diagram:

1	Choose File > New > Database > Database Model to start a new model. Or open an existing one created with the Database Model template.

2	Choose Database > Reverse Engineer to begin working in the Reverse Engineer Wizard.

3	On the first screen, do the following:

From the Installed Visio Drivers list, choose the driver. If you have not already associated a Visio® Enterprise enhanced driver with a particular 32-bit ODBC data source, click Setup to create the association now.

Under Data Sources, choose the source of the database you’re updating, then click Next. If you have not already created a data source for the existing database, click New, then follow the instructions in the wizard.

When you create a new source, Visio Enterprise adds its name to the Data Sources list.

4	In the Connect Data Source dialog box, type a user name and password, then click OK. In the Select Database dialog box, choose the database you want to reverse engineer, then click OK.

5	Check the options for the type of information you want to extract, then click Next.

6	Check the tables (and views, if any) that you want to extract, or click Select All to extract them all, then click Next.

7	If you checked Stored Procedures in step 5, on the next screen check the procedures that you want to extract, or click Select All to extract them all, then click Next.

8	Verify that you are extracting the information you want, then click Finish.

The wizard extracts the selected information and displays notes about the extraction process in the Output window.

9	Drag the tables you want to view from the Tables & Views window onto the drawing page.

For details about working with extracted code, see Viewing and editing code.

For details about working in your model, see Refining database models.

TIP  As you work in your database model diagram, keep it in sync with the database from which it was extracted by choosing Database > Model > Update.

  Overview


Extracting a database schema from Microsoft Repository

 How To	 Related Topics

Using Microsoft Repository you can share database models with colleagues and use your models with other Microsoft Repository–enabled data modeling tools. Visio® Enterprise supports storage and retrieval of your database model in Microsoft Repository using the Database Information Model (DBM) open format. Information about your database model is stored in Microsoft Repository in either a SQL Server or Microsoft Jet database, which is independent of the target implementation platform for your database.

Before you can extract a database schema from Microsoft Repository, you must establish a connection with Microsoft Repository. You do this using the Microsoft DBM Repository driver provided with Visio Enterprise. For details about connecting to Microsoft Repository, see Connecting to Microsoft Repository.


To extract a database model from Microsoft Repository
To extract a database model from Microsoft Repository:

1	In Visio® Enterprise, choose File > New > Database > Database Model, then choose Database > Reverse Engineer.

2	In the Reverse Engineer Wizard that appears, choose Microsoft DBM Repository from the list of available drivers, then click Next.

3	Establish a connection with Microsoft Repository.

For details about connecting to Microsoft Repository, see To connect a Visio Enterprise model to Microsoft Repository.

4	In the Select Schema To Extract dialog box that appears, choose the schema you want to work with, then click OK.

5	Check the options for the type of information you want to extract, then click Next.

6	Check the tables (and views, if any) that you want to extract, or click Select All to extract them all, then click Next.

7	If you checked Stored Procedures as an option in step 5, on this screen check the procedures that you want to extract, or click Select All to extract them all, then click Next.

8	Verify that you are extracting the information you want, then click Finish.

The wizard extracts the selected information and displays notes about the extraction process in the Output window.

9	Drag the tables you want to view from the Tables & Views window onto the drawing page.

For details about working with extracted code, see Viewing and editing code.

For details about working in your model, see Refining database models.

TIP  As you work in your database model diagram, keep it in sync with the database from which it was extracted by choosing Database > Model > Update. For details about updating, see Running the Update Model Wizard.

  Overview


Importing a data model

 How To	 Related Topics

If you have an existing data model created in Visio® Professional, VisioModeler™, or PLATINUM ERwin, you can import it as a physical model in Visio Enterprise. When you work in the Database Model solution, you can import data models in the following formats:

•	Visio Professional 5.0 entity relationship or Crow’s Foot diagram (.vsd) files.

•	VisioModeler (formerly InfoModeler) 1.5, 2.0, 3.0, and 3.1 dictionary model (.imd) files.

•	PLATINUM ERwin 2.6 and 3.0 (.erx) files.

After you import an existing model, Visio Enterprise displays all its tables and views in the Tables & Views window. You can then choose the tables to display on the drawing page, set notation options, and work with the imported model as you would any other Visio Enterprise database model diagram.


Importing VisioModeler dictionary files

 How To	 Related Topics

When you import a VisioModeler™ (formerly InfoModeler®) 1.5, 2.0, 3.0, and 3.1 dictionary model (.imd) file into Visio® Enterprise, Visio Enterprise creates a new model document, recreates each page of the VisioModeler file, and lays out the diagram for you.

All the characteristics of the object types, predicates, and constraints in the model are preserved.


Importing Visio Professional database diagrams

 How To	 Related Topics

You can import diagrams (.vsd files) created using the Crow’s Foot ERD template or the ERD template in Visio® Professional 5.0 into Visio Enterprise.


Importing ERwin models

 How To	 Related Topics

You can import PLATINUM ERwin 2.6 and 3.0 (.erx) files into Visio® Enterprise. An ERwin .erx file is a file exported by ERwin that contains information about a data model in text form. When you import an .erx file, Visio Enterprise creates a model diagram, and converts the information in the .erx file to an IDEF1X data model.

Visio Enterprise draws the entities, attributes, relationships, categories, and text blocks in a logical source model diagram. ERwin subject areas are converted to pages in the source model document. ERwin domains are automatically converted to Visio Enterprise portable data types when Visio Enterprise has a database driver equivalent to an ERwin target server. If Visio Enterprise does not have an equivalent database driver, a warning message appears and Visio Enterprise maps the ERwin data type to a default data type instead.

Visio Enterprise does not import the following ERwin features: stored procedures, custom trigger code, or ERwin displays.


To import a model file from another program
To import a model file from another program:

1	Choose File > New > Database > Database Model to open a new Visio Enterprise database model document.

Or open an existing Visio® Enterprise database model document.

2	Choose Database > Import/Export > Import <model type>.

Choose the Import ERwin ERX, Import Visio Database Diagram, or Import VisioModeler IMD Model command.

3	Type the path and file name for the model you want to import. Or click the Browse button to locate the model file, then click Open.

4	Click OK in the Import dialog box.

Visio Enterprise imports the file and displays its progress in the Output window. The imported tables are displayed in the Tables & Views window.

5	In the Tables & Views window, select the tables you want to model, then drag them onto the drawing page.

  Overview


Customizing your database model diagram

 How To	 Related Topics

You can customize the Visio® Enterprise environment while you work in the Database Model solution by customizing the behavior of the shapes that represent tables, columns, and relationships in your database model. For example, you can

•	Set the behavior when you remove a table or foreign key in a database model diagram.

•	Choose how foreign keys are propagated.

•	Determine how name conflicts are resolved and how default name prefixes are created for the elements in a database model.

To customize these preferences, choose Database > Modeling Preferences. The preferences you set remain in effect across all Visio Enterprise sessions until you choose different settings.


To set behavior options for a database model diagram
To set behavior options for a database model diagram:

1	Open the Database Model file that represents your model.

2	Choose Database > Modeling Preferences.

The Database Modeling Preferences dialog box opens.

3	On the Logical Diagram tab in the Database Modeling Preferences dialog box, choose the options you want to set from the following:

When Removing An Object From The Diagram Specifies whether to remove a deleted object only from the drawing page, retaining it in the Tables & Views window, or remove it from the drawing file (and model) altogether.

Show Relationships After Adding Table To Diagram Shows relationships on the current page between a newly added table and any other tables on the page.

Show Relationships After Adding Type To Diagram Shows relationships between a newly added type in the diagram and any other types on the page.

Sync Conceptual And Physical Names In New Tables And Columns When Typing Changes how the Table Properties and Column Properties dialog boxes work so that typing a name in one field automatically creates the name in the other field.

4	Click the Logical Misc tab, then choose the options you want to set from the following:

FK Propogation Specifies the behavior when adding or deleting foreign keys in a model or when Visio Enterprise encounters a name conflict when a foreign key is added.

Default Name Prefixes Specifies the prefix that is added to the suffix under Default Name Suffixes to form the default name for objects added to a model.

Default Name Suffixes Specifies the suffix that is added to the prefix under Default Name Prefixes to form the default name for objects added to a model.

FK Name Generation Option Specifies how to construct the default foreign key name that appears in a model.

5	When you are finished, click OK.

  Overview


Next steps

 Related Topics

Typically, your next task is to update and refine your database model using the tools in Visio® Enterprise. The following topics provide the information you need.

If you want to	See
Refine your model	Refining database models
Synchronize the model with the database you reverse engineered	Updating models and databases
Print or preview a report of your model	Creating reports


Refining database models

 Related Topics

Visio® Enterprise offers sophisticated functionality in the Database Model solution for working with tables, columns, views, and relationships. As you refine a database model in Visio Enterprise, you can define and edit settings such as data types, referential integrity, indexes, and extended attributes. After you refine your database model, you can check it for both logical and physical validity, and then generate a new or update an existing database.

This set of topics explains how to use the advanced features in Visio Enterprise to refine database models. This set of topics assumes that you are familiar with database concepts and architecture.

Topics in this section

Working with tables

Working with columns

Working with data types

Working with views

Working with relationships

Creating and editing indexes

Setting extended attributes

Validating a database model

Next steps


Working with tables

 How To	 Related Topics

With Visio® Enterprise, you can add new tables to and remove existing tables from the database model, and you can edit table properties. All of your changes are reflected in the database schema when you generate a new database or update an existing one.

The way tables are displayed in Visio Enterprise depends on whether you choose to display the model in IDEF1X notation or Relational notation, the level of detail you want to display, and whether you choose to display relationships using Crow’s Foot notation.

Tables displayed in IDEF1X notation appear as rectangles with the table’s conceptual name above. Tables displayed in Relational notation have a shaded box at the top of the table containing the table’s conceptual name.

IDEF1X tables are classified as independent or dependent; independent tables include no foreign keys as primary keys and are drawn as rectangles with square corners, while dependent tables include one or more foreign keys as primary keys and are drawn as rectangles with rounded corners. There is no graphical notation for independent and dependent tables in models displayed using Relational notation.


To add a new table to the database model
To add a new table to the database model:

1	Open the Visio® Enterprise Database Model file that represents your model.

2	From either the Entity Relationship or Object Relational stencil, drag an Entity shape onto the model drawing page.

Visio Enterprise adds a table to your model. You can set the table attributes, add columns, and specify relationships with other tables in the model.

  Overview


To remove a table from the database model
To remove a table from the database model:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	Select the table you want to remove, then press Delete.

3	In the Delete Object dialog box, choose Yes if you want to remove the table from the model and the Tables & Views window, or No to remove it from the drawing page but not from the model or the Tables & Views window.

NOTE  If you customize the default modeling preferences for removing objects from the diagram to an option other than Ask User What To Do, the Delete Object dialog box will not appear. To check your settings for object removal, choose Database > Modeling Preferences, then click the Logical Diagram tab.

  Overview


To edit the properties of a table in the database model
To edit the properties of a table in the database model:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	Right-click the table whose properties you want to edit, then choose Table Properties from the shortcut menu.

TIP  You can also double-click the table shape to display the Table Properties dialog box.

3	In the Table Properties dialog box, click the tab that contains the information you want to edit, and make your changes. The database model diagram updates automatically. You can choose Edit > Undo to reverse the changes.

Following is a brief description of each tab and the options it contains.

Definition tab Specify physical and conceptual names for the table and choose whether to synchronize the names. You can specify a name space value for the table to distinguish it from similarly named tables in the model. This tab also displays information reported by the host database management system (DBMS) about the database owner and the path of the source database. The defining type field is available only when a table is empty, and lists all composite data types so you can create a typed table.

Primary ID tab Edit, define, or delete primary keys from a list of available columns and choose whether to create an index on primary keys.

Indexes tab Create, edit, define, rename, delete, or set extended attributes for indexes. Use this tab to specify the type of index you create for a particular column.

Triggers tab Add, edit, remove, or preview the code for triggers that are included in the DDL (Data Definition Language) script created when you use the Generate Wizard or Update Database Wizard.

Check tab Add, edit, remove, or preview the code for check clauses that are included in the DDL script created when you use the Generate Wizard or Update Database Wizard.

Extended tab Set DBMS-specific extended attributes for use with the Visio Enterprise database drivers.

Notes tab Type text to document the table. Text entered here appears in the Verbalizer window.

NOTE  To get online help for an option on a tab, click the [image] in the dialog box, then click the option for which you want help. Or right-click the option, then choose What’s This.

4	Click the Close button or press Alt+F4 to close the dialog box.

TIP  You can set the Pushpin ( [image]) in the Table Properties dialog box if you want the dialog box to stay open while you edit a series of database components in the model.

  Overview


Using categories

 How To	 Related Topics

Visio® Enterprise supports category tables, which are subtypes of a parent table. For example, an employee’s record (the generic parent) might contain a job-type column (the category discriminator); category entities in this example might include engineer, technician, and administrator. To define a category table, use the Category shape, the Parent To Category shape, and the Category To Child shape from the Entity Relationship or Object Relational stencil.

If a category table contains all possible subtypes, it is considered complete; if it contains only some possible subtypes, it is considered incomplete. Double lines in a Category shape indicate a complete category; a single line indicates an incomplete category. You can easily toggle a category between complete and incomplete, and you can use the Table Inheritance Properties dialog box to define the category’s discriminator attribute on the parent table. Using a category establishes a foreign key relationship from the parent table to the child tables connected to the Category shape.

The Employee table uses the Type column as the discriminator attribute; the single line on the category connector indicates an incomplete category. The three child tables below are only some of the possible job descriptions for that category.

The Category shape connected to this Customer table indicates a complete category. All entries must be either wholesale or retail customers.


To define a category in a model
To define a category in a model:

1	Drag a Category shape to your database model.

2	Choose the connector tool ( [image]) from the Standard toolbar, then click the Parent To Category shape. Position the connector tool over the center of the parent table so that the table is outlined, press the mouse button, then drag to the center of the Category shape. When the Category shape is outlined, release the mouse button.

Both connection points appear red.

3	With the connector tool still selected, click the Category To Child shape. Position the connector tool over the center of the Category shape so that it is outlined, press the mouse button, then drag to the center of the child table. When the child table is outlined, release the mouse button.

Both connection points appear red, and the primary keys from the parent table are added as foreign keys to the child table.

4	Repeat step 3 for each child table.

  Overview


To set a Category connector shape as complete or incomplete
To set a Category connector shape as complete or incomplete:

1	Right-click the Category connector shape you want to change, then choose Table Inheritance Properties from the shortcut menu.

2	In the Table Inheritance Properties dialog box, clear the Category Is Complete check box to mark the category as incomplete, or select the Category Is Complete check box to mark the category as complete.

An incomplete shape displays a single line below the circle.

A complete shape displays a double line below the circle.

  Overview


To specify a category discriminator attribute
To specify a category discriminator attribute:

1	Right-click the Category shape you want to change, then choose Table Inheritance Properties from the shortcut menu.

2	On the Definition tab, click This Attribute, then choose a column from the parent table as the category discriminator.

  Overview


 Working with columns

 How To	 Related Topics

Visio® Enterprise makes it easy to refine the tables in your database model by adding, editing, and deleting columns and their properties. You can add columns, remove columns, edit column properties, and change the order of the columns in a table.

NOTE  For details about adding complex data types to database models that will be implemented on an object-relational DBMS, see Working with data types.

Columns in a table can be identified as a primary key; primary keys have unique values, which are used to identify instances of the table in a database, and each table must have a primary key in order to pass logical validation. Columns can also be identified as foreign keys. A foreign key describes the relationship of a child table to a parent table by defining the attribute from the parent table that the child table inherits.

Visio Enterprise automatically creates unique indexes on primary keys. Other columns can also be assigned a non-unique index called the inversion entry. Indexes optimize the performance of your database for data retrieval. You can edit the properties of an index in the Table Properties dialog box.

NOTE  You can edit default column name suffixes in the Database Modeling Preferences dialog box. Choose Database > Modeling Preferences, then click the Logical Misc tab.


To add a new column to a table
To add a new column to a table:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	In the database model diagram, right-click the table to which you want to add the column, then choose Edit Columns from the shortcut menu.

3	In the Columns In Table dialog box, click the Add button.

A new column is added to the table using the default naming conventions you specified in the Modeling Preferences dialog box.

4	Click Close.

  Overview


To remove a column
To remove a column:

1	Right-click the table from which you want to remove the column, then choose Edit Columns from the shortcut menu.

2	In the Columns In Table dialog box, select the column you want to remove, then click the Remove button.

3	Click Close.

  Overview


To reorder the columns in a table
To reorder the columns in a table :

1	Right-click the table in which you want to reorder the columns, then choose Edit Columns from the shortcut menu.

2	In the Columns In Table dialog box, select a column you want to reorder, then drag it up or down to move it to a new location in the column list.

3	Click Close.

  Overview


To edit or create a primary key
To edit or create a primary key:

1	Right-click the table to which you want to add a primary key, then choose Table Properties from the shortcut menu.

2	In the Table Properties dialog box, click the Primary ID tab.

3	Select the column you want to be the primary key in the Available Columns list, and use the Move button (>>) to move it to the Primary ID list.

Optional Customize the physical name and key type.

4	Click the Close button or press Alt+F4 to close the dialog box.

NOTE  The position of primary keys in your table depends on the Order option you select on the Table tab in the Database Document Options dialog box (Database > Document Options). If you choose Physical Order, primary keys are displayed in the order they will have in the database. Otherwise, primary keys are displayed at the top of a table.

  Overview


To edit the properties of a column
To edit the properties of a column:

1	Right-click the table with the column you want to edit, then choose Edit Columns from the shortcut menu.

2	In the Columns In Table dialog box, select the column you want to edit, then click the Edit button.

3	In the Column Properties dialog box, click the tab that contains the information you want to edit, and make your changes. The database model diagram updates automatically. You can choose Edit > Undo to reverse the changes.

Following is a brief description of each tab and the options it contains.

Definition tab Enter or edit a Physical Name to control the column’s name in the database or a Conceptual Name to control the column’s name as it appears in the database model. Check Allow NULL Values to make the column optional rather than mandatory.

Data Type tab Display either Portable or Physical Data Types, and assign Data Types to columns.

Collection tab Specify whether the column is a single value or contains a collection of information. Collections may be sets, lists, or multiple values, and may only be used in object-relational models.

Check tab Edit, add, remove, or preview check clauses which are included in the DDL script that you can create in the Generate Wizard or Update Database Wizard.

Extended tab Set DBMS-specific extended attributes for use with the Visio® Enterprise database drivers.

Notes tab Type text to document the column. Text entered here appears in the Verbalizer window.

NOTE  To get online help for an option on a tab, click the [image] in the dialog box, then click the option for which you want help. Or right-click the option, then choose What’s This.

4	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In View dialog box.

TIP  You can set the Pushpin ( [image]) in the Column Properties dialog box if you want the dialog box to stay open while you edit a series of elements in the diagram.

  Overview


Working with data types

 How To	 Related Topics

Visio® Enterprise supports both relational and object-relational database models, so you can work with both simple and composite data types. Simple data types, used in both types of models, support one data value per column. Object-relational databases support composite data types, in which a column can contain multiple values or fields, and those fields can have different data types. The Visio Enterprise data type support ensures the database schemas you generate from your models are as efficient as possible.

When you use shapes from the Object Relational stencil to refine an object-relational database model, you can define composite data types and specify collection types for columns. Composite data types allow you to encapsulate fields that might contain multiple values of different data types. Collection types allow multiple instances of a given value to be stored in a field. Both composite data types and collection types can optimize the performance of a database by storing data in a single entity rather than using foreign keys and secondary tables.


Setting data types

 How To	 Related Topics

You can set data type properties for any column in the Column Properties dialog box. A column’s data type specifies

•	The kind of data that the column can contain.

•	The types of operations you can perform on the data in the column.

•	Column characteristics such as physical storage requirements.

With Visio® Enterprise, you can use generic portable data types, which can be mapped to multiple databases at the time you generate a schema, or physical data types, which are specific to a target database. When you generate a database schema, Visio Enterprise maps portable data types to the closest data type supported by the DBMS you specify. This feature offers flexibility if you are designing a database model that will be implemented on more than one type of DBMS or if you are transferring a legacy database from one DBMS to another.

When you work with portable data types, you can use the default specifications or define a user-defined data type for easy reuse. User-defined data types can save time and ensure consistency by providing a reusable data type with a standard definition. You choose Database > User-Defined Types to add, remove, and edit custom data types. You can also copy definitions from existing user-defined data types to new ones.

NOTE  Before you set data type information in your database model, you should be familiar with the data types and storage requirements supported by your target DBMS. Refer to the documentation for your target DBMS to determine which data type to assign.


To set the data type for a column
To set the data type for a column:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	Right-click the table containing the column whose data type you want to set, then choose Edit Columns from the shortcut menu.

3	Select the column you want to edit in the Columns In Table dialog box, then click the Edit button.

4	In the Column Properties dialog box, click the Data Type tab.

5	Select the data type options you want to set for the column.

•	User Defined Type Select a data type that you have defined in the User-Defined Types dialog box.

•	Category Choose a data type category.

•	Type Refine the basic data type by choosing a specific type available for that category.

•	Size Set a size value for the data type (not available for all data types).

•	Length or Precision Length sets the maximum length of a field (not available for all data types). Precision specifies how many integers are used to express the value (not available for all data types).

•	Scale Specifies the number of digits after the decimal point for numeric data types (not available for all data types).

•	Show Portable Data Type Displays the generic data type that will be mapped to a physical data type when the database is generated.

•	Show Physical Data Type Displays the physical data type that the column will be mapped to when the database is generated. You can set specific physical data types for some components of a database model. Physical data types are specific to the selected database driver.

6	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In View dialog box.

  Overview


To create a user-defined data type
To create a user-defined data type:

1	Choose Database > User Defined Types.

NOTE  The Database command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	In the User-Defined Types dialog box, click Add.

3	In the Add New User Defined Type dialog box, enter a name.

Optional To base your new data type on an existing one, check Copy From, then select the name of an existing user-defined data type.

4	Click OK.

5	Optional Enter notes about the data type in the Description box.

6	Specify portable data type characteristics for the data type.

7	Click OK.

  Overview


To remove a user-defined data type
To remove a user-defined data type:

1	Choose Database > User Defined Types.

NOTE  The Database command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	In the User-Defined Types dialog box, select the data type you want to delete.

3	Click Remove, then click OK.

TIP  You can also delete a user-defined type from the Types window. To do so, choose Database > View > Type Selector, right-click the user-defined type, then choose Delete from the shortcut menu.

  Overview


Using collection types and composite data types in a database model

 How To	 Related Topics

Collection types and composite data types can be used in models that will be implemented on object-relational databases. Collection types can be assigned to any column in any table, but unless you display physical data types in the model there is no way to see at a glance where they have been used. Composite data types appear as distinct elements in a diagram, and can be specified as the data type. You can use composite data types to create typed tables or views.


To set a collection type
To set a collection type:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	Right-click the table containing the column you want to specify as a collection type, then choose Edit Columns from the shortcut menu.

3	In the Columns In Table dialog box, select the column you want to set as a collection type, then click the Edit button.

4	In the Column Properties dialog box, click the Collection tab, then choose one of the following collection types:

Unordered, Non-Repeating Group Of Values (Set) Allows population instances of a field in a column to have multiple values, but each value must be unique. The values do not have any sorting criteria on them; therefore they are unordered.

Ordered Group Of Values (List) Creates an ordered collection of elements that allows duplicate elements. Each element in the collection has an ordinal position. An ordinal position is a position by which a value can be accessed. Because two values can be the same, they are differentiated by their ordinal positions.

Unordered, Repeating Group Of Values (Multiset) Creates a collection of elements that can have duplicate values. The elements do not have ordinal positions.

Single Value (Column) Specifies that no collection type property is applied. This is the default type.

5	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In View dialog box.

  Overview


To display collection types
To display collection types:

1	Choose Database > Document Options.

NOTE  The Database command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	In the Document Options dialog box, click the Table tab, then select Show Physical. The collection type name will precede the name of any column assigned a collection type.

3	Click OK.

  Overview


To create a composite data type
To create a composite data type:

1	From the Object Relational stencil, drag a Type shape to your database model.

2	Right-click the Type shape, then choose Edit Fields from the shortcut menu.

3	In the Field In Type dialog box, click Add.

4	Optional To edit a field name, click the Edit button, click the Definition tab in the Field Properties dialog box, then enter a new name in the Physical Name field. To edit the other properties of the field, click the appropriate tab in the Field Properties dialog box and enter the information.

5	Click the Close button or press Alt+F4 to close the Field Properties dialog box, then click Close to close the Fields In Type dialog box.

  Overview


To add a composite data type to a table
To add a composite data type to a table:

1	Right-click a table in the database model and choose Edit Columns from the shortcut menu.

2	In the Columns in Table dialog box, click Add.

3	With the new column name selected, click Edit.

4	In the Column Properties dialog box click the Data Type tab, then choose the complex data type from the User Defined Type list.

5	Optional To edit the other properties of the column, click the appropriate tab in the Column Properties dialog box and enter the information.

6	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In View dialog box.

  Overview


Working with views

 How To	 Related Topics

With a view, you can combine columns from several tables, and you can perform operations on the data from those columns. Views provide a convenient way to access information needed by the users of a database without changing the structure of the tables in the database. For example, a view for invoices might combine columns from a table containing customer contact information, another containing employee information, and a third containing order details. Because the data in the view is inherited from the columns in the other tables, database accuracy and efficiency are improved.

In Visio® Enterprise, you can create views by dragging and dropping the View shape on the Entity Relationship or Object Relational stencil to your database model diagram. When you create a view using the View shape, Visio Enterprise automatically creates SQL code to define the view. You can also use the Code Editor to define DBMS-specific views.


To add a view to a model
To add a view to a model:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	From either the Entity Relationship or Object Relational stencil, drag a View shape onto the model drawing page.

Visio Enterprise adds a view to the model. You can set view properties and add columns to define the view.

  Overview


To remove a view from the database model
To remove a view from the database model:

1	Select the view you want to remove, then press Delete.

2	In the Delete Object dialog box, choose Yes if you want to remove the view from the model and the Tables & Views window, or No to remove it from the drawing page but not from the model or the Tables & Views window.

NOTE  If you changed the default modeling preferences for removing objects from the diagram to an option other than Ask User What To Do, the Delete Object dialog box will not appear. To check your settings for object removal, choose Database > Modeling Preferences, then click the Logical Diagram tab.

  Overview


To assign columns from tables to a view
To assign columns from tables to a view:

1	Right-click the view to which you want to add columns, then choose Edit Columns from the shortcut menu.

2	In the Columns In View dialog box, click Add once for each column you want to add.

3	Select a column name and click Edit to define the column’s properties.

4	In the View Column Properties dialog box, click the Source tab. Click Known Column In Another Table Or View, then click Change.

5	In the Pick A Column dialog box, choose a column from the list of tables and views in your model, then click OK.

6	Repeat steps 3 through 5 for each column you added.

7	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In View dialog box.

  Overview


To edit the properties of a column in a view
To edit the properties of a column in a view:

1	Right-click the view containing the columns you want to modify, then choose Edit Columns.

2	In the Columns In View dialog box, click to select the column you want to modify, then click Edit.

3	In the View Column Properties dialog box, click the tab that contains the information you want to edit, and make your changes. The database model diagram updates automatically. You can choose Edit > Undo to reverse the changes.

Following is a brief description of each tab and the options it contains.

Source tab Identify the source of the information for that column. Enter SQL code in the Definition Rule field to create a calculated value.

Definition tab Enter Physical and Conceptual Names for the view, and specify whether the column will accept NULL values.

Data Type tab Specify a data type for the column. Select Show Portable Data Type to assign a user-defined data type or a portable data type, or select Show Physical Data Type to assign a DBMS-specific data type.

Notes tab Type text to document the view. Text entered here appears in the Verbalizer window.

4	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In View dialog box.

  Overview


To edit the properties of a view
To edit the properties of a view:

1	Right-click the view whose properties you want to edit, then choose View Properties from the shortcut menu.

TIP  You can also double-click the View shape to display the View Properties dialog box.

2	In the View Properties dialog box, click the tab that contains the information you want to edit, and make your changes. The database model diagram updates automatically. You can choose Edit > Undo to reverse the changes.

Following is a brief description of each tab and the options it contains.

Definition tab Enter Physical and Conceptual Names for the view, and specify a Name Space. To create a typed view, select a type from the Defining Type list.

Join Criteria tab Define the foreign key relationships for columns included in the view by joining columns from associated tables.

SQL tab View the SQL code that defines the view. Uncheck Auto-Generate to edit the code directly in the dialog box, or click Code Properties to open the Code Editor.

Extended tab Set DBMS-specific extended attributes for use with the Visio® Enterprise database drivers.

Notes tab Type text to document the view. Text entered here appears in the Verbalizer window.

3	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In View dialog box.

  Overview


To edit a view's SQL code
To edit a view’s SQL code:

1	Right-click the view whose SQL code you want to edit, then choose View Properties from the shortcut menu.

2	In the View Properties dialog box, click the SQL tab.

3	Uncheck Auto-Generate. To edit the code, type in the SQL tab’s code edit box, or click Code Properties to open the Code Editor.

On the Body tab of the Code Editor, you can edit the SQL code. On the Properties tab of the Code Editor, you can create a mirror file and choose a name for the view code, which appears in the Code window. Click OK when you are done.

4	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In View dialog box.

  Overview


Working with relationships

 How To	 Related Topics

As you refine your database model, you can add and remove relationships between tables, and edit relationship properties. Relationship shapes are one-dimensional connectors that you can glue between two table shapes to define a relationship between them. Relationships indicate visually how the tables in your database model interact with one another, and editing their properties enables you to control how data is stored and retrieved in the database. Relationships in a model are characterized in terms of direction, referential integrity, and cardinality.

The originating table in a relationship is known as the parent; the receiving table that assumes or inherits attributes from the parent table is known as the child. When you add a relationship to your model, you connect first to the parent table and then to the child table in order to establish the relationship’s direction. Attributes inherited from the parent table appear as foreign keys in the child table. Setting a parent/child relationship between tables enables you to specify referential integrity, which establishes the rules that govern what effect a change to the parent table has on the child table. The cardinality of a relationship defines the ratio of instances of a data value in the child table that can exist in proportion to a related data value in the parent table.

NOTE  The notation for your model determines how the relationship’s directional is displayed. In Relational notation, an arrow points to the parent table. In IDEF1X notation, and when Drawn With Crow’s Feet is selected on the Relationship tab of the Database Document Options dialog box, a dot or crow’s foot symbol indicates the child table.


To add a relationship
To add a relationship:

1	Choose File > New > Database > Database Model to start a model in Visio® Enterprise using the Database Model template. Add at least two table entities to your model.

2	Choose the connector tool ( [image]) from the toolbar. Position the connector tool over the center of the parent table so that the table has a heavy outline surrounding it, press the mouse button, and drag the shape to the center of the child table. When the child table also displays a heavy outline, release the mouse button. Both connection points should appear red, and any primary keys in the parent table are added as foreign keys to the child table.

TIP  Alternatively, you can drag a Relationship shape onto the drawing page. Hold the Ctrl key while you drag one end of the Relationship shape to the Entity shape, then release the mouse. Repeat for the other end of the Relationship shape.

3	Define the relationship by editing its properties.

  Overview


To remove a relationship
 To remove a relationship:

•	Click to select the relationship you want to delete, then press the Delete key.

  Overview


To edit the properties of a relationship
To edit the properties of a relationship:

1	Choose the pointer tool ( [image]) from the Standard toolbar.

2	Right-click the relationship whose properties you want to edit, then choose Relationship Properties from the shortcut menu.

TIP  You can also double-click the relationship shape to display the Relationship Properties dialog box.

3	In the Relationship Properties dialog box, click the tab that contains the information you want to edit, and make your changes. The database model diagram updates automatically. You can choose Edit > Undo to reverse the changes.

Following is a brief description of each tab and the options it contains.

Definition tab Associate columns in the parent and child tables and enter role names for foreign keys.

Name tab Enter a verb phrase, inverse phrase, physical name, or notes about the relationship.

Miscellaneous tab Choose the type of cardinality, whether the relationship is identifying or nonidentifying, and whether the relationship is optional.

Ref. Integrity tab Choose options that determine the effect on the child table when information in the parent table is updated or deleted.

NOTE  To get online help for an option on a tab, click the [image] in the dialog box, then click the option for which you want help. Or right-click the option, then choose What’s This.

4	Click the Close button or press Alt+F4 to close the dialog box.

For details about how to edit or define referential integrity and extended attributes, see Creating and editing indexes and Setting extended attributes.

  Overview


Setting referential integrity and cardinality

 How To	 Related Topics

The principle of referential integrity requires that the foreign key values of a child table are either NULL or match exactly those of the primary key in the parent table in order to ensure validity in the physical implementation of the database. For example, a referential integrity rule may require that all employees be assigned to one of the company departments that are stored in another table. This way, no employee can be assigned to a department that does not exist.

To maintain referential integrity, you can specify rules that govern how a change to the data values in one table in the relationship affects the data values in the other table. When you enforce referential integrity, the foreign key (FK) relationships between related tables remain valid when you insert, update, or delete records.

You can edit referential integrity settings in the Relationship Properties dialog box; Visio® Enterprise automatically generates referential integrity constraints between related tables using the settings you define.

When a record in a database is updated or deleted, you can choose any of the actions shown.

No Action Makes no automatic changes to the foreign key.

Cascade Makes the same changes to the foreign key as are made to the primary key.

Set NULL On Parent Delete, Set NULL sets all foreign key columns in matching rows to NULL. On Parent Update, Set NULL sets the entire foreign key to NULL.

Do Not Enforce Turns off referential integrity for the relationship.

NOTE  Not all DBMSs support all the options available on the Ref. Integrity tab of the Relationship Properties dialog box. Refer to the documentation for your target DBMS when setting referential integrity options. You should choose the settings that your DBMS supports.


Displaying referential integrity and cardinality notation

 How To	 Related Topics

You can choose to display referential integrity notation next to the relationship lines in your model. Visio® Enterprise displays the notation in this form: u:C, where “u” is the type of change (update or delete) and “C” is the type of action (No Action, Cascade, or Set NULL).

Referential integrity notation	Triggered action
u	On update
d	On delete
R	No action
C	Cascade
N	Set NULL
(Blank)	Do not enforce referential integrity
Visio Enterprise also supports cardinality notation, which describes how many instances of a value in a child table connect to how many instances of a value in a parent table.

When a model uses IDEF1X notation, you can check Cardinality (IDEF1X) on the Relationship tab of the Database Document Options dialog box to display cardinality settings.

NOTE  Visio Enterprise does not support many-to-many relationships in IDEF1X notation.


To edit referential integrity settings
To edit referential integrity settings:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	Right-click the Relationship shape whose referential integrity settings you want to edit, then choose Relationship Properties from the shortcut menu.

TIP  You can also double-click the relationship to display the Relationship Properties dialog box.

3	In the Relationship Properties dialog box, click the Ref. Integrity tab.

4	Select On Parent Update and On Parent Delete options.

5	Click the Close button or press Alt+F4 to close the dialog box.

  Overview


To show referential integrity settings in a database model
To show referential integrity settings in a database model:

1	Choose Database > Document Options.

NOTE  The Database command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	On the Relationship tab, check Referential Integrity, then click OK.

For details about setting referential integrity in Visio Enterprise, see Creating and editing indexes.

  Overview


To show cardinality notation in your database model
To show cardinality notation in your database model:

1	Choose Database > Document Options.

NOTE  The Database command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	On the Relationship tab, check Cardinality (IDEF1X), then click OK.

NOTE  Visio Enterprise Relational notation supports cardinality, but does not display it graphically.

  Overview


Creating and editing indexes

 How To	 Related Topics

An index helps your database system find and sort records faster. Creating indexes for columns you plan to search frequently can significantly improve your database’s performance. You can define indexes manually, and you can modify the indexes that Visio® Enterprise creates automatically. For example, primary keys (PK) automatically generate a unique constraint. In an IDEF1X model, alternate keys (AK) generate a unique index, and inversion entries (IE) generate a non-unique index or a constraint. When Visio Enterprise generates an index for you it automatically assigns it a name.

NOTE  Some database applications have a limit on the type and number of indexes you can create per table. For specific information, see your DBMS documentation. Visio Enterprise checks for this limit during physical validation.

You can define indexes and edit their properties using the Table Properties dialog box. For example, you can edit the name of an index, change index options, rearrange the order of columns in an index, or set extended attributes for an index. Extended attributes for an index are driver-specific and are not supported by all DBMSs.


To create an index
To create an index:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	Right-click the table to which you would like to add an index, then choose Table Properties from the shortcut menu.

TIP  You can also double-click the table shape to display the Table Properties dialog box.

3	In the Table Properties dialog box, click the Indexes tab, then click New.

4	In the Create Index dialog box, enter a name for the index, then click OK.

5	From the Available Columns list, select the name of each column that you want to include in this index, then click the Move (>>) button.

6	Check the Asc check box to create an index with ascending sort order, or clear it to create one with descending sort order.

7	From the Index Type drop-down list, choose an option to create a unique or non-unique index.

8	Optional Click the Options button to set driver-specific extended index attributes.

9	Click the Close button or press Alt+F4 to close the dialog box.

  Overview


To remove an index
To remove an index:

1	Right-click a table containing indexed columns, then choose Table Properties from the shortcut menu.

2	In the Table Properties dialog box, click the Indexes tab.

3	In the Index Name list, select the name of the index you want to delete, then click Delete.

4	Click the Close button or press Alt+F4 to close the dialog box.

  Overview


To edit index properties
To edit index properties:

1	Right-click a table containing indexed columns, then choose Table Properties from the shortcut menu.

2	In the Table Properties dialog box, click the Indexes tab.

3	In the Index Name list, select the name of an index.

4	Edit the properties of the index. For example, to enter a new name for the index, click Rename. To choose a unique or non-unique index, choose an option in the Index Type box.

5	Click the Close button or press Alt+F4 to close the dialog box.

  Overview


To add, remove, or reorder columns in an index
To add, remove, or reorder columns in an index :

1	Right-click a table containing indexed columns, then choose Table Properties from the shortcut menu.

2	In the Table Properties dialog box, click the Indexes tab.

3	In the Index Name list, select the name of the index you want to edit.

4	Do one of the following:

•	To add a column to an index, select the name of the column in the list of Available Columns, then click the Move (<<) button.

•	To remove a column from an index, select the name of the column in the list of Indexed Columns, then click the Move (>>) button.

•	To reorder a column, select the name of the column in the list of Indexed Columns, hold down the mouse button, then drag the column name to its new position in the list.

5	Click the Close button or press Alt+F4 to close the dialog box.

  Overview


Setting extended attributes

 How To	 Related Topics

You can set extended attributes for a table, column, or relationship using the Table Properties, Column Properties, or Relationship Properties dialog box. Extended attributes are specific to a target DBMS, and allow you to control more precisely the way elements defined in your model are mapped to a target database.

Using the Extended tab, you can specify DBMS-specific attributes for a table. The attributes you can set depend on the capabilities supported by the DBMS.

For example, with Informix you can use extended attributes to choose dynamic storage options for tables. Similarly, if you are targeting your model to SQL Server 6.5, you can specify the segment of the target database on which a table will be stored.


To set extended attributes for a table or view
To set extended attributes for a table or view:

1	Open the Visio® Enterprise Database Model template file that represents your database model.

2	Right-click the table or view whose extended attributes you want to define, then choose Table Properties or View Properties from the shortcut menu.

NOTE  Microsoft Repository drivers support extended attributes for views.

3	In the Table Properties or View Properties dialog box, click the Extended tab.

4	From the Installed Visio Drivers list, select a Visio Enterprise database driver that matches the DBMS you plan to use.

For example, if you want to generate a database schema for a Sybase database, choose the Visio Enterprise Sybase driver.

5	Click the Change button.

Visio Enterprise opens a driver-specific extended attributes dialog box.

NOTE  If a particular DBMS does not support extended attributes, Visio Enterprise dims the Change button on the Extended tab, and the option is unavailable.

6	Fill in the appropriate information and choose any options that you want.

7	Click OK.

8	Click the Close button or press Alt+F4 to close the dialog box.

  Overview


To set extended attributes for a column
To set extended attributes for a column:

1	Right-click the table containing the column whose extended attributes you want to define, then choose Edit Columns from the shortcut menu.

2	In the Columns In Table dialog box, select a column to edit, then click Edit.

3	In the Column Properties dialog box, click the Extended tab.

4	From the Installed Visio Drivers list, select a Visio Enterprise database driver that matches the database you plan to use.

For example, if you want to generate a database schema for a Sybase database, choose the Visio® Enterprise Sybase driver.

5	Click the Change button.

Visio Enterprise opens a driver-specific extended attributes dialog box.

NOTE  If a particular DBMS does not support extended attributes, Visio Enterprise dims the Change button on the Extended tab, and the option is unavailable.

6	In the dialog box that appears, specify the extended attribute information for the DBMS, then click OK.

7	Click the Close button or press Alt+F4 to close the Column Properties dialog box, then click Close to close the Columns In Table dialog box.

  Overview


Validating a database model

 How To	 Related Topics

You can choose to check the validity of your model at any time during the modeling process. Visio® Enterprise checks your database model for both logical and physical validity and displays progress notes, warnings, and errors in the Output window. Using the information in the Output window you can quickly navigate to any warning conditions or errors in the model. When you use the Generate, Update Model, and Update Database Wizards, Visio Enterprise automatically validates your database model before taking any action.

During logical validation Visio Enterprise checks your model for adherence to generally accepted database design standards. For example, circular table relationships produce an error during logical validation; similarly, using the same physical name for two columns in a table produces an error. During physical validation, Visio Enterprise checks your database model for compliance with DBMS-specific requirements. For example, an Oracle DBMS allows only one column in a table to use the Long data type; if a table contains more than one column using this data type, validation will generate an error. It is possible to have a logically correct database model that contains physical errors, especially if you are reverse engineering a legacy database to a new DBMS.

When you use the Database > Model > Check command to validate a model, Visio Enterprise displays any progress notes, errors, or warnings in the Output window. If the model contains any critical errors, you must fix them before you can use the Generate, Update Model, Update Database, or Transfer commands. Warnings do not prevent you from using these commands.


To correct an error or warning
To correct an error or warning:

1	In the Output window, double-click the error or warning text.

Visio® Enterprise selects the structure in the diagram containing the error.

2	Right-click the error text in the Output window, then choose Get Help For Message from the shortcut menu. Or select the text, then press F1.

Visio Enterprise displays an explanation for the error and how to resolve it.

3	Make the necessary correction.

4	Repeat steps 1–3 for each error or warning in the Output window.

  Overview


To validate your model
To validate your model:

1	Choose Database > Model > Check.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	In the Output window, double-click any error or warning to highlight the source of the problem in the model.

3	Correct the cause of the error or warning.

4	Repeat steps 2 and 3 for each error or warning in the Output window.

  Overview


Next steps

 Related Topics

After you have refined and validated the logical model, you are ready to generate or update a database schema. You may also want to create a report, so you can review all tables or types in a design or see a summary of the model.

If you want to	See
Generate a new database schema	Generating a database schema
Update the schema of an existing database	Running the Update Database Wizard
Synchronize a model with the database from which it was extracted	Running the Update Model Wizard
Create a report	Creating reports


Reverse engineering database code

 Related Topics

A database model can include code in the form of stored procedures, triggers, check clauses, and so on. This code is preserved when you reverse engineer a database, so that you can work with code components of your model in Visio® Enterprise. As you refine your database model, you can also create new code, and view and edit the code that was extracted by the Reverse Engineer Wizard. If you are reverse engineering a database to a new platform, you can transfer the code as well and view platform-specific implementations of code as you work. In addition, Visio Enterprise supports the use of version control software, so that you can track revisions to all the code associated with your database model.

This set of topics describes the tools you use to reverse engineer code in a database model. In this context, “code” refers to stored procedures, functions, view definitions, triggers, check clauses, and other platform-specific code that you can view and edit in the Database Model solution.

Topics in this section

Extracting code with the Reverse Engineer Wizard

Viewing and editing code

Working in the Code Editor

Adding code to a model

Saving and synchronizing code

Transferring code to a new target platform

Next steps


Viewing and editing code

 How To	 Related Topics

A database model can include code associated with objects in the database or the database itself. When you reverse engineer a database that contains code, such as stored procedures, Visio® Enterprise lists each code component in the order it was extracted in the Code window. Later, if you run the Generate Wizard or Update Database Wizard to create a DDL (Data Definition Language) script of the changes you’ve made to the model, the code is generated in the script in the same order as it appears in the Code window.

Platform-specific versus portable code

As you work in a model, you can create, view, and edit platform-specific or portable code associated with a database or its components. Only platform-specific code appears in the Code window, which includes all the code extracted from a database and any new code you write that is intended for a particular database, which you specify by setting the current database driver for the model to that database. Portable code is generic SQL generated by Visio Enterprise and not associated with any database driver. After you generate or update a database for a model that includes portable code, Visio Enterprise generates a platform-specific copy of the code for that database, which you can view in the Code window.

The Code window designates each of the following types of platform-specific code with a unique icon:

 Stored procedures User-defined functions and other procedures that can be invoked with a CALL statement and are stored in an executable format in the database.

 Function Procedural code that returns a value.

 Triggers Code associated with specific database tables that is invoked automatically whenever rows of the associated table are inserted, updated, or deleted.

 Check clauses Code applied to a table or column that enforces constraints.

 View Definitions Code usually containing SELECT statements that assigns the results of a query to a named, table-like structure called a view.

 Raw DDL Other code that does not fall into one of the above categories; for example, synonyms or package constructs in an Oracle database.

Not all platforms support these types of code. For example, although you can define a new view in your database model, you cannot use the model to generate a Corel Paradox database with a view, because Paradox does not support views. You need to know the capabilities of your target database when creating code for a model.

Supporting multiple platforms

A model can be used to generate different target databases, but the Code window always displays a platform-specific version of the code based on the currently selected database driver. You can have multiple versions of the same code in your model to support the different databases you intend to generate, but the Code window shows only one at a time. To associate code with a different target database, you transfer it to the intended platform. You can switch between code implementations displayed in the Code window by changing the current driver.

Global versus local code

Code is shown in the Code window as global or local. Global code refers to any stored procedure, function, view definition, or raw DDL code that is not associated with a specific table. Local code refers to triggers or check clauses for a specific table or column in your model.

The Code window lists all the stored procedures, triggers, functions, and other code associated with a particular database driver for your model.

The name of the current default driver appears in the title bar.


To open the Code window
To open the Code window:

•	Choose Database > View > Code Selector.

Options in the Code window

If you want to	Do this in the Code window
Reorder code items in the list	Drag an item to a new position in the list.
Remove code from your model	Select an item in the list, then press Delete.
Display only global code	Right-click any item, then choose Show Only Global Code.
Print the code for an item	Right-click an item in the list, then click the Print button ( [image]).
Create a new stored procedure, function, view definition, or other DDL code	Click New, then work in the Code Editor.
View an item in the Code Editor	Select an item in the list, then click Edit. Or double-click the item.
  Overview


Working in the Code Editor

 How To	 Related Topics

To view or edit the code shown in the Code window, you can display the Code Editor, which has a toolbar for common editing tasks and options for changing the window’s appearance.

You can customize the editing environment to suit your working style. The Code Editor supports drag-and-drop text editing as well as an unlimited number of undo and redo instances. Toolbar buttons provide easy access to the Cut, Paste, Undo, and Redo commands. Pause the pointer over a button to see what it does. In addition, you can change the colors used in the Code Editor, specify indentation preferences, and more by changing the window properties.

As you work in the Code Editor, you can set options to automatically indent code, color keywords, and insert blank code skeletons.


To open code in the Code Editor
To open code in the Code Editor:

1	If the Code window is not already open, open it by choosing Database > View > Code Selector.

NOTE  This command is available only when you start a model with the Database Model template.

2	Click a code item in the list, then click Edit. Or double-click a code item in the list.

3	View and revise code on the following tabs:

Body tab Enter and edit SQL statements. To edit, use the toolbar buttons, or right-click in the code to see additional options.

Properties tab Enter or revise the code component’s name, choose the type of code it represents, and create a mirror file for saving the code component externally.

Notes tab Add code comments that will be generated in the DDL script for the model.

4	To set preferences for working in the Code Editor, click the Window Properties button ( [image]). Or right-click in the code, then choose Properties from the shortcut menu.

5	In the Window Properties dialog box, choose options from the various tabs. The table below shows commonly used options.

6	When you are finished, click OK to return to the Code Editor. To close the Code Editor, click OK.

  Overview


To work in the Code Editor

If you want to	Do this
Automatically indent lines as you enter code to follow the scoping rules of SQL	Right-click in the code, then choose Properties. On the Language/Tabs tab, choose Follow Language Scoping, then click OK.
Assign keyboard shortcuts	Right-click in the code, then choose Properties. On the Keyboard tab, choose a command, then a new key assignment.
Change spaces to tab characters	Select the code, right-click the selection, then choose Tabify.
Change keyword colors in the code	Right-click in the code, then choose Properties. On the Color/Fonts tab, choose an item, then choose a new foreground or background color.
  Overview


Adding code to a model

 How To	 Related Topics

You can define new stored procedures, triggers, views, and other code as you work in your database model. To add global code to your model quickly, you use the Code window to create a new stored procedure, function, view definition, or other platform-specific DDL code. To create new local code, you work with the Properties dialog boxes for the different shapes.

When you create new code for a model, the code is platform-specific. Visio® Enterprise associates the code with the database driver currently selected in the Database Drivers dialog box (Database > Drivers). You can later transfer the code, or a copy of it, to a different platform.

After you write the code and define its properties in the Code Editor, it appears as an item in the Code window. If you later generate a DDL script from your model, or create an update script, the new code appears in the script in the order it appears in the Code window..


Creating stored procedures, functions, and other global code

 How To	 Related Topics

Stored procedures, functions, and other platform-specific types of DDL code are referred to as global code in Visio® Enterprise. You can work in the Code Editor to write new code of any of these types by entering SQL statements and defining properties for the code item. Visio Enterprise adds new global code to the list of code items in the Code window.

Your target database may support types of code other than stored procedures and functions. For example, if your target platform is an Oracle database, you can create a package to group together related variables, exceptions, and so on. In your database model, you can create such platform-specific code types by specifying Raw DDL as the code type. When you use the Generate Wizard or Update Database Wizard to generate a DDL script for a model that contains code marked as Raw DDL, the wizard outputs whatever you defined in the Body tab of the Code Editor.


To create a stored procedure, function, or other global DDL code
To create a stored procedure, function, or other global DDL code:

1	If the Code window is not already open, choose Database > View > Code Selector.

NOTE  This command is available only when you start a diagram with the Database Model template.

2	Click New to open the Code Editor.

3	On the Properties tab, type a name for the code item. Under Type, choose Stored Procedure, Function, or Raw DDL.

4	On the Body tab, enter SQL statements. When you’re finished, click OK.

The code is added to the Global Code list in the Code window.

  Overview


Defining view code

 How To	 Related Topics

In Visio® Enterprise, you can work directly in the Code Editor to define a view intended for a particular target platform. When you define a view in this manner, the view is treated like any other code. The view is associated with the current database driver and is not visually represented in your model except as an item in the Code window.

When you reverse engineer a database that contains views, they appear in the Tables & Views window, so that you can drop a view into your model, where it is represented by the View shape. Because a view is defined by SQL code, it also appears in the Code window.

Views can represent platform-specific or portable code. Only platform-specific views appear in the Code window. Here, the PORTABLE view was created by using the View shape, then using the Auto-Generated option, which generates SQL code automatically for the view.

For most target databases, you create a new view in the Code Editor by using the CREATE VIEW statement to name the view that contains the records specified by the SELECT statement. For example, the following code creates the view MYVIEW that displays the ITEMREQUIRED record from the ORDERS table:

CREATE VIEW myview AS

SELECT itemRequired FROM ORDERS;

If you want to create a view that also appears in your model on the drawing page, you can use the View shape. With the View shape, you can quickly define a simple view for which Visio Enterprise can automatically generate SQL code that defines the view’s contents. Or you can overwrite the generated code for a View shape by disabling the Auto-Generated option in the SQL tab of the View Properties dialog box, and then typing in the Code Editor. When you customize the code for a View shape, Visio Enterprise associates the view definition with the currently selected database driver, and the view code appears in the Code window for that driver.


To define a view
To define a view:

1	If the Code window is not already open, choose Database > View > Code Selector.

NOTE  This command is available only when you start a diagram with the Database Model template.

2	Click New to open the Code Editor.

3	On the Properties tab, type a name for the code item. Under Type, choose View Definition.

4	On the Body tab, enter SQL statements. When you’re finished, click OK.

The code is added to the Global Code list in the Code window.

If you create a view based on another view, you must make sure that the original view appears in the Code window before any views based on it. This is because Visio® Enterprise generates the code in a DDL script in the order that code items appear in the Code window.

  Overview


To reorder views in the Code window
To reorder views in the Code window:

1	If the Code window is not already open, choose Database > View > Code Selector.

NOTE  This command is available only when you start a diagram with the Database Model template.

2	Drag the view you want to reorder to a new position in the window.

  Overview


Creating trigger code

 How To	 Related Topics

Because a trigger is associated with a specific database table, you create a trigger in Visio® Enterprise by working in the Table Properties dialog box. When you reverse engineer a database that contains triggers, or add a new trigger to a table in your model, the trigger code is shown on the Triggers tab of the Table Properties dialog box and is listed under Local Code in the Code window.

A trigger can be executed—or triggered—when specific database events occur, such as when a record is inserted, updated, or deleted. For example, you can use a trigger to log audit information to a separate table, calculate derived information such as the extended cost of price times quantity, notify logged-on users of limits that have been exceeded, or call a stored procedure.


To create a trigger for a table
To create a trigger for a table:

1	Right-click a table, then choose Table Properties from the shortcut menu.

2	On the Triggers tab, click Add to open the Code Editor.

3	On the Properties tab, type a name for the trigger.

4	On the Body tab, enter SQL statements. When you’re finished, click OK to return to the Table Properties dialog box.

The trigger is added to the Local Code list in the Code window. You can now close the Table Properties dialog box, or refine other table properties.

  Overview


Creating check clauses

 How To	 Related Topics

You can use a check clause to enforce constraints on a table or column. The Code window lists all check clauses extracted from a database you reverse engineer as local code. You can also create a new check clause by working in the Table Properties or Column Properties dialog box.

When you create a new check clause for a table, the code is specific to the platform currently selected in the Database Drivers dialog box and appears in the Code window for that driver. When you create a new check clause for a column, you can define the check clause yourself for your intended target database, or have Visio® Enterprise automatically generate one based on a range or set of values that you specify. Automatically generated check clause code for a column does not appear in the Code window. If you then generate or update a DDL script for your target database, Visio Enterprise adds the check clause to the Code window for that database.

For example, you can add a check clause to the ActBalance record in the Customers table to check a customer’s account balance. You can specify a range of values to be checked, and then Visio Enterprise will automatically generate a check clause. To customize the check clause for a particular database, you can edit the code. Visio Enterprise then adds the check clause to the Code window for the currently selected database driver.


To create a check clause for a table
To create a check clause for a table:

1	Right-click a table, then choose Table Properties from the shortcut menu.

2	On the Check tab, click Add to open the Code Editor.

3	On the Properties tab, type a name for the check clause.

4	On the Body tab, enter SQL statements. When you’re finished, click OK to return to the Table Properties dialog box.

The check clause is added to the Local Code list in the Code window. You can now close the Table Properties dialog box, or refine other table properties.

  Overview


To create a check clause for a column
To create a check clause for a column:

1	Right-click a table, then choose Edit Columns from the shortcut menu.

2	Select a column from the Columns In Table list, click Edit, then click the Check tab.

3	To generate a check clause based on a range of values automatically, click Show Value/Range, then enter values in the From and To boxes to define the range of the constraint. Click Add.

4	To generate a check clause for a set of values automatically, type a value in the Value box, then click Add. Repeat for additional values, clicking Add after each.

5	To customize or view the check clause code, click Show Check Clause. To modify the check clause, click Customize, then type in the Check Clause box.

The customized check clause is added to the Local Code list in the Code window.

6	To close the Column Properties dialog box, click the Close box. Or keep it open and refine other column properties.

7	Click Close to close the Columns in Table dialog box.

NOTE  An automatically generated check clause does not appear in the Code window, but a customized check clause does.

  Overview


Saving and synchronizing code

 How To	 Related Topics

You can save the code you add to a database model diagram as a file separate from your model. By creating a mirror file, you create a file of your stored procedure, trigger, or check clause that you can save, work with outside of Visio® Enterprise, and store using your version control software.

Once you’ve created a mirror file of your code, you can keep it in sync with your model by choosing Database > Code > Synchronize. Then if you edit the code outside of Visio Enterprise, or change it while working in the Code Editor, the changes are synchronized between the model and the mirror file.

You can create a mirror file for any code in your model, so that it can be stored in your version control software. To synchronize the model code with its mirror file, choose Database > Code > Synchronize.


To save code in a mirror file
To save code in a mirror file:

1	Choose Database > View > Code Selector to open the Code window, if it is not already open.

2	In the Code window, select the code you want to save, then click Edit. Or double-click the code.

The Code Editor opens.

3	Click the Properties tab. In the File box, enter a path and file name, or click Browse to locate the file you want, then click OK.

4	In the Code Editor, click OK.

  Overview


To synchronize code in a model with a mirror file
To synchronize code in a model with a mirror file:

1	Choose Database > Code > Synchronize.

The Synchronize dialog box opens. If a change has been made to any stored procedure, trigger, or check clause in the model, the code is listed under Differences.

2	Select the stored procedure, trigger, or check clause you want to synchronize, then choose one of the following:

Update Mirror File(s) Now Click to change the code stored in the mirror file to match the model.

Update Model Now Click to change the code stored in the model to match the mirror file.

Visio® Enterprise lists the changes it makes to the model or mirror file.

3	Click Refresh to update the list of code in need of synchronization. Or click Close.

  Overview


Transferring code to a new target platform

 How To	 Related Topics

A single model that contains code can be used to generate a schema for multiple platforms. Using the Transfer command (Database > Code > Transfer), you can move your code, or a copy of it, from one platform to another. As you work in your model, you can switch between platform-specific versions of the code by changing the default driver for the model. The Code window provides access to only the version of code for the currently selected database driver. You can create as many platform-specific copies of your code as you need.

To enable a model to support multiple database platforms, you can transfer code from one platform to another by choosing Database > Code > Transfer.

Transferred code is platform-specific. When you use the Transfer command, Visio® Enterprise moves or copies all the code in the model as text without performing any changes. You can then customize the code as necessary for the intended platform by opening it in the Code Editor.


To transfer code
To transfer code:

1	Choose Database > Code > Transfer.

The Transfer dialog box appears.

2	Under Operation, choose Move to transfer all the code to the new platform, or choose Copy to transfer a copy of the code, leaving the original implementation.

3	Under Source DBMS, select the database you reverse engineered. Under Target DBMS, select the new platform for which you want to generate the code.

4	Click OK.

When the transfer is complete, a message appears in the Info tab of the Output window.

  Overview


To switch between code implementations in your model
To switch between code implementations in your model:

1	Choose Database > Drivers.

2	In the Database Drivers dialog box, click the Drivers tab. Under Installed Visio Drivers, choose the driver for the platform to which you transferred the code.

3	Click OK.

The list in the Code window is updated to display the platform-specific code implementation.

  Overview


Next steps

 Related Topics

After editing code in a model or adding new code, you can generate a new database schema based on the model, or update an existing one. Or you can synchronize your model with the database schema from which it was extracted to see what other changes may have occurred. For additional information, see the following topics.

If you want to	See
Generate a new database schema	Generating a new database
Update the schema of an existing database	Updating a database from the model
Synchronize the model with an existing database schema	Updating a model from a database


Connecting to a DBMS

 Related Topics

Using Visio® Enterprise, you can create a database model independent of its implementation on any particular database management system (DBMS). At the time you connect to your target database you can choose a DBMS-specific driver, which makes it easy to implement a database model on more than one DBMS. For example, you could prototype a database that targets one DBMS and implement it on another; or, you could extract a model from a legacy database, reuse the components, and implement the resulting model on a different DBMS.

You must specify a Visio Enterprise database driver, an associated vendor-supplied ODBC (Open Database Connectivity) driver, and a data source when you use Visio Enterprise to

•	Reverse engineer an existing database using the Reverse Engineer Wizard.

•	Generate a new physical database using the Generate Wizard.

•	Update a model from an existing database using the Update Model Wizard.

•	Update an existing database from a model using the Update Database Wizard.

This set of topics explains how to set up and use the Visio Enterprise database drivers in conjunction with 32-bit ODBC drivers to connect to and exchange information about a database model with a DBMS.

Topics in this section

About using database drivers

Configuring and associating a Visio Enterprise driver

Creating and setting up an ODBC data source

Connecting to Microsoft Repository

Next steps


About using database drivers

 How To	 Related Topics

A database driver is a file that Visio® Enterprise uses to communicate with your DBMS in order to reverse engineer a database model, update a model from a database, generate a new database, and update an existing database. You use Visio Enterprise database drivers in conjunction with vendor-supplied 32-bit ODBC drivers to connect directly to your DBMS. Each driver tells Visio Enterprise the kind of DDL (Data Definition Language) script to generate, how to map data types and constraints, and how to specify extended attributes for your particular database application.

Visio Enterprise uses its own drivers in combination with a 32-bit ODBC driver to connect to a database.


Enhanced database drivers in Visio Enterprise

 How To	 Related Topics

The Visio® Enterprise Setup program automatically installs the Visio Enterprise database drivers enhanced to work specifically with the Database Model template and the Microsoft ODBC Data Access Pack version 3.5 ODBC drivers, which are provided with Visio Enterprise. To determine which Visio Enterprise drivers you have installed on your system, choose Database > Drivers.

Visio Enterprise includes its own database drivers for the following DBMSs:

Desktop database drivers

•	Corel Paradox 7, 8

•	Inprise (formerly Borland) dBASE III, IV, 5; Inprise Paradox 3, 4.x, 5

•	Microsoft Access 2.0, 7.0, 97; Microsoft FoxPro 2.0, 2.5, 2.6; Microsoft Visual FoxPro 5.0, 6.0

Client/server database drivers

•	IBM DB2 Universal Database 2.1.2, 5.0

•	Informix Dynamic Server, with support for the Universal Data Option; INFORMIX–SE 5.0; INFORMIX–OnLine 7.x; INFORMIX–Universal Server

NOTE  Informix Software has renamed its line of database servers. INFORMIX–OnLine is now named Informix Dynamic Server. INFORMIX–Universal Server is now named Informix Dynamic Server with Universal Data Option.

•	Microsoft SQL Server 4.2, 6.0, 6.5, 7.0

•	Oracle 7.x, 8

•	Sybase Adaptive Server 10, 11.x (formerly Sybase System); Sybase Adaptive Server Anywhere 6.x; Sybase SQL Anywhere 5.x

Microsoft Database Information Model driver

•	Microsoft DBM Repository

Generic driver

If you are developing a database for a DBMS other than one listed here, you can use the Visio Enterprise ODBC Generic driver with a vendor-supplied 32-bit ODBC driver to connect to your DBMS. When you use the generic driver, the information you can reverse engineer, generate, or update depends on the capabilities of the vendor-supplied 32-bit ODBC driver. The vendor-supplied driver must be:

•	From an ODBC Data Access Pack greater than version 2.0.

•	ODBC Level 1-compliant or greater.

You can also check the Visio Corporation Web site (www.visio.com) for new and updated database drivers.


Before you connect

 How To	 Related Topics

The procedures in this Help assume that you have installed and correctly configured the appropriate database software, client software if necessary, and 32-bit ODBC drivers. If you have difficulty, your system administrator can assist you with

•	Obtaining access and rights to your particular desktop or SQL-based client/server database.

•	Installing and configuring the appropriate client software on your local drive for a client/server database.

•	Installing the correct vendor-supplied 32-bit ODBC drivers for your DBMS on your local drive.

•	Configuring vendor-supplied 32-bit ODBC database drivers correctly using the Microsoft ODBC Data Source Administrator.

For details about configuring and creating a data source using the Microsoft ODBC Data Source Administrator, see that product’s online help.

When you have completed these tasks, you can configure the Visio® Enterprise database driver and associate it with the 32-bit ODBC driver for your specific DBMS.


Configuring and associating a Visio Enterprise driver

 How To	 Related Topics

You can configure your Visio® Enterprise and ODBC drivers at any time by choosing Database > Drivers. When you configure your database drivers, you specify a default driver and default mapping for column creation, associate a 32-bit ODBC driver with a particular Visio Enterprise driver, and configure default settings such as preferred settings for portable data types.

NOTE  For an association between a Visio Enterprise driver and a 32-bit ODBC driver to work properly, the drivers you select must be designed to work with each other. For example, you would associate a Visio Enterprise Sybase driver with a vendor-supplied ODBC Sybase driver.

When you reverse engineer, generate a database, update a model, or update a database, Visio Enterprise provides a filtered list of available data sources based on the database driver associations you established.

To associate a 32-bit ODBC driver with a Visio Enterprise driver, choose Database > Drivers, select an installed Visio driver, and then click Setup.

On the ODBC Drivers tab, select the ODBC driver you want to use, and then click OK to close both dialog boxes.


To configure a Visio Enterprise database driver
To configure a Visio Enterprise database driver:

1	Choose Database > Drivers.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	In the Database Drivers dialog box, click the Drivers tab. Under Installed Visio Drivers, select the driver you want to configure.

Optional Click Set As Default to make this driver the default.

3	On the Default Mapping tab, under Default Category Type For Column Creation, choose the default category type for column creation and set the default data Category, Type, Size, Length, Precision, or Scale for each category type.

4	On the Driver Misc tab, choose default settings for DDL script generation used by the Generate and Update Database Wizards.

5	When you finish configuring a database driver, do one of the following:

•	Click OK to close the dialog box.

•	Click the Drivers tab, then associate the driver with a 32-bit ODBC driver by following the steps in the next procedure.

  Overview


To associate a Visio Enterprise driver with a 32-bit ODBC driver
To associate a Visio Enterprise driver with a 32-bit ODBC driver:

1	Choose Database > Drivers.

2	In the Database Drivers dialog box, click the Drivers tab. Under Installed Visio Drivers, choose the driver with which you want to associate an ODBC driver.

3	Click Setup.

4	Click the ODBC Drivers tab, then choose the name of the 32-bit ODBC driver that you want to associate with the selected Visio® Enterprise driver.

NOTE  For an association between a Visio Enterprise driver and a 32-bit ODBC driver to work properly, the drivers you select must be designed to work with each other. For example, you would associate a Visio Enterprise Sybase driver with a vendor-supplied ODBC Sybase driver.

5	Optional Click the Preferred Settings tab, then choose default settings for portable data types and the default data source.

NOTE  The contents of the Preferred Settings tab are driver-specific and reflect the specific features available in the DBMS. For example, the Oracle and IBM DB2 drivers have options for creating case-insensitive table and column identifiers during DDL generation.

6	Click OK to close the driver-specific dialog box, then click OK to close the Database Drivers dialog box.

TIP  You can also set up or modify the association between a 32-bit ODBC driver and a Visio Enterprise driver in the Reverse Engineer Wizard, Generate Wizard, Update Model Wizard, or Update Database Wizard on the screen where you select connection options. On that screen, select an installed Visio driver, then click Setup. On the ODBC drivers tab, select the ODBC driver you want to associate with the selected Visio driver, then click OK.

  Overview


Creating and setting up an ODBC data source

 How To	 Related Topics

After you configure and associate drivers, you can create a data source for a particular database. A data source includes the data you want to access and crucial information for accessing that data, such as the name of the database, the server on which it resides, and network information. Visio® Enterprise refers to a data source by its data source name (DSN).

NOTE  You can create a 32-bit ODBC data source using the Microsoft ODBC Data Source Administrator, or you can create a data source as you work in the Reverse Engineer Wizard, Generate Wizard, Update Model Wizard, or Update Database Wizard. Each of these wizards contains a screen on which you can select connection options and establish ODBC DSNs.

You can edit and manage data sources you create in Visio Enterprise as you would any other data source using the ODBC Data Source Administrator. If you need help setting up, modifying, or removing an ODBC data source for your particular type of database, see the online help that accompanies the Microsoft ODBC Data Source Administrator.


To create a data source using the ODBC Data Source Administrator
To create a data source using the ODBC Data Source Administrator:

1	In the Windows Control Panel, double-click the 32bit ODBC icon.

2	On the User DSN tab in the ODBC Data Source Administrator, click Add.

IMPORTANT  Visio® Enterprise supports only User DSNs. It does not support data sources created as File DSNs or System DSNs.

3	Choose the 32-bit ODBC driver you want to use for the data source, then click Finish.

The ODBC driver you select may be associated with one or more Visio Enterprise database drivers.

4	In the driver-specific dialog box that opens, type a Data Source Name and description, choose the database, then provide the required information. When you are finished, click OK.

The ODBC Data Source Administrator creates the data source.

5	Click OK to close the ODBC Data Source Administrator.

  Overview


To create a new ODBC data source in a Visio Enterprise wizard
To create a new ODBC data source in a Visio Enterprise wizard:

1	On the wizard screen where you can select connection options, click the New button.

2	Select User Data Source as the type of new data source you want to create, then click Next.

NOTE  Visio® Enterprise supports only User DSNs. It does not support data sources created as File DSNs or System DSNs.

3	Select the 32-bit ODBC driver you want to use, click Next, then click Finish.

The 32-bit ODBC driver you select may be associated with one or more Visio Enterprise database drivers.

4	In the driver-specific dialog box that opens, type the Data Source Name, select the database, then provide the required information. When you are finished, click OK.

The ODBC Data Source Administrator creates the data source, and Visio Enterprise adds it to the list of available data sources on the screen where you select connection options.

  Overview


Connecting to Microsoft Repository

 How To	 Related Topics

Visio® Enterprise supports storage and retrieval of your database model in Microsoft Repository using the Database Information Model (DBM) open format. Using Microsoft Repository you can share database models with colleagues and use your models with other Microsoft Repository–enabled data modeling tools. Information about your database model is stored in Microsoft Repository in either a SQL Server or Microsoft Jet database , which is independent of the target implementation platform for your database.

Before you can generate, reverse engineer, or update a Visio Enterprise database model using Microsoft Repository, you must establish a connection with Microsoft Repository. You do this using the Microsoft DBM Repository driver provided with Visio Enterprise.


To connect a Visio Enterprise model to Microsoft Repository
To connect a Visio Enterprise model to Microsoft Repository:

1	In a Visio® Enterprise database model file, choose either Database > Update, Database > Reverse Engineer, Database > Generate, or Database > Model > Update to open a wizard.

2	On the connections page of the Update Database Wizard, Reverse Engineer Wizard, Generate Wizard, or Update Model Wizard, choose the DBM Repository Driver from the list of Installed Visio Drivers, then click Next.

3	In the Connect Microsoft Repository dialog box, choose a connection method from the options below:

NOTE  If you connect to a Jet or SQL Server repository database, you must either use an existing repository database or create an empty repository database before you establish a connection. Microsoft Repository will not automatically create a repository database for you.

To Jet Repository To connect to a Jet repository database, type the following statement in the Connection String box:

DBQ=DatabasePath\Database.mdb

Replace DatabasePath with the directory in which the repository database resides and Database.mdb with the repository database file name.

To SQL Server Repository Database To connect to a SQL Server repository database, type the following statement in the Connection String box:

SERVER=ServerName;DATABASE=DatabaseName

Via A Data Source Name If you have an existing Jet or SQL Server repository database, you can connect to it using a DSN that has been configured using the ODBC Data Source Administrator. Select the DSN from the list provided.

NOTE  For details about establishing a DSN using the ODBC Data Source Administrator, see Creating and setting up an ODBC data source.

4	If necessary, type your User Name and Password, then click OK.

5	Continue following the steps in the Generate Wizard, Reverse Engineer Wizard, Update Database Wizard, or Update Model Wizard.

  Overview


Next steps

 Related Topics

Setting up database drivers and data sources are necessary steps if you want to connect to a database in order to reverse engineer a model, generate a new database, update a model from a database, or update a database from a model. For additional information, see the topics below.

If you want to	See
Reverse engineer a database	Reverse engineering an existing database
Generate a database schema or generate a DDL script	Generating a database schema
Update a model from a database and reconcile any conflicts between them	Updating a model from a database
Update or forward engineer a database from a model	Updating a database from the model


Generating a database schema

 Related Topics

The process that Visio® Enterprise uses to generate a database schema for a specific database management system (DBMS) involves gathering the information from the database model, checking the model for errors, checking the model’s semantics for compliance with DBMS-specific requirements, and mapping the validated model to an optimally normalized database schema.

This set of topics explains how to use the Generate Wizard to generate a database schema either as a DDL (Data Definition Language) script or directly by connecting to a desktop or client/server database, or to Microsoft Repository.

Topics in this section

Preparing to generate a database or a DDL script

Generating and editing a DDL script

Generating a new database

Next steps


Preparing to generate a database or a DDL script

 How To	 Related Topics

With Visio® Enterprise, you use the Generate Wizard to produce a database schema based on your model. The Generate Wizard takes you through the process of choosing options and drivers, connecting to a data source, and previewing changes.

As a final step, the Generate Wizard checks your model to ensure that the information in the model does not violate any restrictions imposed by the target DBMS. If your model is free of critical errors, Visio Enterprise generates a new database, a DDL script, or both for you. If there are errors, Visio Enterprise identifies them so you can correct them in the model.


 Before you generate

 How To	 Related Topics

Before you try to use the Generate Wizard, you should refine your model and make sure it is error-free by validating it. If your model contains errors when you choose Database > Generate to open the Generate Wizard, the Output window displays them, and the wizard will not open.

The procedures in this Help assume that you have installed and correctly configured the appropriate database software and drivers. If you have difficulty, your system administrator can assist you with

•	Obtaining access and rights to your particular desktop or SQL-based client/server database.

•	Installing and configuring the appropriate client software on your local drive for a client/server database.

•	Installing the correct vendor-supplied 32-bit ODBC (Open Database Connectivity)drivers for your DBMS on your local drive.

•	Configuring the vendor-supplied 32-bit ODBC database drivers correctly using the Microsoft 32-bit ODBC Data Source Administrator.


Choosing a generate option for a DBMS

 How To	 Related Topics

In the process of generating a database schema, Visio® Enterprise uses enhanced database drivers in conjunction with Microsoft– or vendor-supplied ODBC drivers to connect online to your desktop or client/server database, or Microsoft Repository. The options available for generating a new database depend on the capabilities of your DBMS, and not all servers support the direct creation of a database through 32-bit ODBC drivers. Before you generate, you should determine the database generation method that works for your particular DBMS.

In the Generate Wizard, you can generate a DDL script from your model, create a new database by connecting online to your DBMS, or both simultaneously.

Generate a DDL script from your model. You can preview and edit the SQL code in a DDL script, and then run the script in your database application. Visio Enterprise does not require a 32-bit ODBC data source to generate a DDL script.

Create a new database by connecting online to your target DBMS and directly generating a database schema. The Create Database option is available only if your server supports creation of a database through 32-bit ODBC drivers.

IMPORTANT  The following DBMSs do not support the direct creation of a database through 32-bit ODBC drivers: all Informix DBMSs, all Oracle DBMSs, and Visual Fox Pro 3.0. When you select the drivers for these DBMSs, the Create Database option on the second screen of the Generate Wizard is dimmed.

If you are generating a database schema for a DBMS that does not support the direct creation of a new database, you can

•	Generate a DDL script, which your DBMS can use to generate the database.

•	Use your DBMS to create a new (empty) database on your server and then instruct Visio Enterprise to generate the database schema for it.

Use your DBMS to create a new (empty) database on your server and use Visio Enterprise to generate a database schema for it. You can then instruct Visio Enterprise to connect to an existing (empty) database using a data source that you prepared with your 32-bit ODBC Data Source Administrator, and then create the tables in the database.

For details about creating a new (empty) database on your server, see the documentation for your particular DBMS. For details about configuring and creating a data source using the 32-bit ODBC Data Source Administrator, see the documentation that comes with the 32-bit ODBC. For details about the Visio Enterprise database drivers, see Connecting to a DBMS.


Generating and editing a DDL script

 How To	 Related Topics

If your DBMS does not support direct creation of a new database, you can use Visio® Enterprise to generate a DDL script that contains the database schema for the database. You may also prefer to work with a DDL script rather than create a database directly if you want to manually review or edit the script before implementing it. Visio Enterprise does not need to connect to an ODBC data source to generate a DDL script.

On the first screen of the Generate Wizard, you can choose to generate a DDL script, a database schema, or both simultaneously.

NOTE  This section describes generating a DDL script. The following topic, Generating a new database,” describes generating a database schema.

DDL scripts, written in SQL code, are used to create, modify, and delete databases objects such as tables, columns, rules, and indexes. Visio Enterprise can generate a text file of the DDL script based on your model. You can then review, modify, and run the text file from your DBMS.

Once you have generated a DDL script, you can open, review, and edit the script using any text editor. You might want to review a script to verify its contents, or edit it to modify a specific component’s properties.

NOTE  The Generate Wizard only creates a DDL script; it does not implement it.


To generate a DDL script
To generate a DDL script:

1	Choose Database > Generate to begin working in the Generate Wizard.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	On the first screen of the Generate Wizard, check Generate A Text File Of The DDL Script, enter a path and file name for the new script in the File Name box, then click Next.

3	On the second screen, do the following:

From the Installed Visio Drivers list, choose a driver. If you have not already associated a Visio Enterprise driver with a particular 32-bit ODBC data source, click Setup to create the association and to specify default and preferred settings for the 32-bit ODBC driver now.

Enter a database, directory, file, or schema name, then click Next.

4	On the third screen, review the list of tables and views that will be created in the new database. To make any changes to the model, click Cancel. To revise options in the Generate Wizard, click Back. Click Next to continue.

The Generate Wizard then validates the database schema.

5	On the last screen, review the Warnings box for any warnings that resulted during the validation of the database schema. The Output window lists any errors and warnings along with a brief description; double-clicking an error in the Output window takes you to the source of the error in your model.

TIP  To see explanations and tips for correcting errors and warnings, select an error message in the Output window and press F1.

6	Click Finish to create the DDL script, or click Cancel to return to the Output window and correct any errors or warnings.

When the Generate Wizard finishes, Visio Enterprise asks if you want to view the generated DDL script. Be sure to review the script carefully before you run it in your DBMS. You can open, review, and edit the script in any text editor.

NOTE  If you modified and then ran the DDL script that Visio Enterprise generated, you must synchronize the tables in the model with the tables in the new database.

  Overview


To edit a DDL script immediately after you generate it
To edit a DDL script immediately after you generate it:

1	Generate a DDL script following the instructions in the Generate wizard.

2	After you click Finish, you will be asked if you want to view the DDL script. Click Yes.

3	The DDL script opens in Microsoft Notepad. You can view or edit the document.

4	Choose File > Save to save your changes.

5	Choose File > Exit to close Microsoft Notepad.

  Overview


To edit a DDL script at any time
To edit a DDL script at any time:

•	From any text- or code-editing application, open the DDL script you want to view or edit.

The DDL script appears, and you can review or edit it.

  Overview


Generating a new database

 How To	 Related Topics

Once your model is complete, you can use the Generate Wizard to step through the process of generating a new database schema. Whether you are generating a new database, a DDL script, or both simultaneously, choose Database > Generate, and then follow the instructions in the Generate Wizard.

To generate a database schema

To generate a DDL script


To generate a database schema
To generate a database schema:

1	Choose Database > Generate to begin working in the Generate Wizard.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	On the first screen, check Generate New Database.

Optional To store the generated database structure in the source document, check Remember The Generated Database State. Selecting this option creates a record of the extracted database objects, which enables the Update Database and Update Model Wizards to work correctly.

Click Next.

3	On the second screen, do the following:

From the Installed Visio Drivers list, choose a driver. If you have not already associated a Visio Enterprise driver with a particular 32-bit ODBC data source, click Setup to create the association and to specify default and preferred settings for the 32-bit ODBC driver now.

Click Create <item> to create a new database file, or <item> Already Exists to populate an existing file with the contents of your database model. The names of the options change depending on the drivers you have selected. When you have selected a driver for a DBMS that does not support direct creation of a new database, the Create <item> option is dimmed.

Create <item> or Data Source Name Enter a valid Data Source Name if you want to generate a database schema for an empty database you prepared ahead of time using your 32-bit ODBC Data Source Administrator. Or, to create a new, empty database as part of the generation process, click New to create a new data source, then follow the instructions on the screens.

NOTE  If you need more information, see your documentation for the 32-bit ODBC Data Source Administrator.

Enter a file, database, directory, or schema name, then click Next.

<item> Already Exists The wizard will use an existing file, database, directory, or schema. Click Next to continue.

4	Optional In the Connect Data Source dialog box, type a user name and password, then click OK.

5	On the next screen, review the list of tables and views that will be created in the new database. To make any changes to the model, click Cancel. To revise options in the Generate Wizard, click Back. Click Next to continue. The Generate Wizard then validates the database schema.

6	On the last screen, review the Warnings box for any warnings that resulted during the validation of the database schema. The Output window lists any errors and warnings along with a brief description; double-clicking an error in the Output window takes you to the source of the error in your model.

TIP  To see explanations and tips for correcting errors and warnings, select an error message in the Output window and press F1.

7	Click Finish to create the new database schema, or click Cancel to return to the Output window and correct any errors or warnings.

  Overview


Next steps

 Related Topics

You can use the DDL script or database schema you created with Visio® Enterprise in your DBMS to update and maintain a database easily as your company’s information requirements change. After you have reviewed and tested the database, you can archive the project and source documents. If you need to make more changes to the database, you can review the following topics.

If you want to	See
Synchronize your data model after generating a database from a DDL script that you modified	Updating a model from a database
Make changes to the model and alter the database	Updating a database from the model


Updating models and databases

 Related Topics

Visio® Enterprise keeps your model in sync with your database while you work and makes it easy to update the database schema when your model is complete. Whether you have created a new database model or are reverse engineering an existing database, you need a way to synchronize any changes made to the database while you make refinements in your model. Visio Enterprise uses a unique three-way comparison that tracks differences between the model and database, so that you can migrate changes from the model to the database, or from the database to the model.

To update a model from the database, use the Update Model Wizard, which provides options for resolving discrepancies. To update a database from a model, typically the final step in the reverse engineering process, use the Update Database Wizard. This set of topics explains how to use these wizards to synchronize and update models and databases.

Topics in this section

Keeping a model and database in sync

Running the Update Model Wizard

Running the Update Database Wizard

Next steps


 Keeping a model and database in sync

 Related Topics

If the database schema has changed since the last time you generated it, you can update either the database or your model to keep them in sync. For example, in a large project in which several people are concurrently working in a database, someone could add or remove tables from the database after you extracted the schema on which your model is based. Using the Update Model and Update Database Wizards, you can interactively compare a model diagram to a selected database schema, and then address any items that are out of sync.

The automatic three-way comparison process keeps a model in sync with a database by comparing changes in the model and database to the original state of the database at the time you extracted its schema.

To synchronize a model and database, Visio® Enterprise performs the following steps in a process called three-way comparison:

•	Compares the current state of the database schema to its original state when you extracted the schema

•	Compares the original state of the database schema to your model

•	Compares the current state of the database schema to your model

Visio Enterprise evaluates these comparisons to detect any discrepancies between the model and database schema so that you can merge changes and update one or the other as necessary. The three-way comparison process occurs automatically as a step in both the Update Model and the Update Database Wizards. For example, suppose someone deleted a table from the database since the last time you extracted its schema. When you run either the Update Model Wizard or Update Database Wizard, it detects the deleted table and lists it as a conflict. Depending on which wizard you are using, you can resolve the discrepancy in one of these ways:

•	Update the model to match the current state of the database schema, an option in both the Update Model and Update Database Wizards.

•	Change the database schema to match the model, an option in the Update Database Wizard.

•	Return the database to its original state when you extracted the schema, an option in the Update Database Wizard.

•	Ignore the conflict for now.

You can choose, item by item, how to resolve each discrepancy that Visio Enterprise uncovers. Following on the previous example, if you choose to update your model, the table is deleted from the model diagram. If you choose to update the database, the table is added back to the database.

In addition, you can choose to document any conflicts or inconsistencies between the database schema and the model in a log file. You can set this option on the first screen of both wizards.


Updating a model from a database

 How To	 Related Topics

To ensure that your model reflects the current state of the database from which it was generated or extracted, use the Update Model Wizard (Database > Model > Update). The wizard compares the database schema to the schema represented by the model. For each difference that the wizard detects, such as duplicate or missing tables, you can choose whether to update the model to reflect that change.

The Update Model Wizard makes changes to a model to synchronize it with the current state of a database schema.

You should update a model in any of these cases:

•	You want to detect any changes that may have occurred in a database and incorporate them into your model.

•	You used the Generate Wizard to create a DDL (Data Definition Language) script, which you executed to alter your database, and now want to update the model in Visio® Enterprise.


Changes detected by the Update Model Wizard

 Related Topics

The following table describes the differences between each object in a database schema and your model that can be detected by the Update Model Wizard.

Differences between a database schema and model detected by the Update Model Wizard

Database object	Type of change detected
Table	Deleting; changing its check clause, extended attribute, type (for typed tables)
Column	Adding or deleting; changing its name, data type, collection type, check clause, or extended attributes
View	Deleting or changing its structure
Primary key	Adding or deleting; changing its name or structure
Relationship (foreign key)	Adding or deleting; changing its name or structure (columns, cardinality, or referential integrity)
Index	Adding or deleting; changing its columns, collation, or uniqueness
Composite type	Deleting; changing its structure (named, distinct, domain) or alias (for collection type or aliased data type
Field in a type	Adding or deleting; changing its name, structure (data types or, for object-relational, collection type), check clause, or extended attributes
Code	Deleting; changing code statements or code type (stored procedure, function, trigger, etc.)


Updating a database from the model

 How To	 Related Topics

When you use the Update Database Wizard (Database > Update), you can change the structure of a database to match the contents of your model. The Update Database Wizard calculates the net changes to a database schema based on your model, prompts you to resolve any conflicts, and then generates a DDL script or directly alters the database.

You should update a database in any of these cases:

•	You reverse engineered the database in Visio® Enterprise, and now want to forward engineer the changes in your model to the database.

•	After you reverse engineered the database, changes were made to the original database, and now you want to update your model.

•	You created a new model from scratch that you want to insert into an existing database.

The Update Database Wizard makes changes directly to a database or creates a DDL script that you can execute to make changes based on the refinements you have made to a model.

The Update Database Wizard (Database > Update) provides two ways to update the database, directly or through a DDL script.

•	The wizard connects to your target database and directly alters an existing database schema or creates a new one.

•	The wizard creates a DDL script of the changes that you want to make to the database. You can then execute the script from your database application.

If the wizard cannot directly generate update statements for the target database, it may also create a DML (Data Manipulation Language) script to add any data from the existing tables to the newly created ones.


Changes detected by the Update Database Wizard

 Related Topics

The following table describes the differences between each object in a database schema and your model that can be detected by the Update Database Wizard.

Differences between a database schema and model detected by the Update Database Wizard

Database object	Type of change detected
Table	Deleting from model or database; adding to model; changing its name in model; changing its check clause, extended attribute, type (for typed tables) in model or database
Column	Adding to or deleting from model or database; changing its name, data type, collection type, check clause, or extended attributes
View	Deleting from model or database; adding to or changing name in model; changing its structure
Primary key	Adding to or deleting from model or database; changing its name or structure
Relationship (foreign key)	Adding to or deleting from model or database; changing its name or structure (columns, cardinality, or referential integrity)
Index	Adding to or deleting from model or database; changing its columns or uniqueness in model or database; changing collation in database
Composite type	Deleting from model or database; adding to or changing name in model; changing its structure (named, distinct, domain) or alias (collection type or aliased data type) in model or database
Field in a type	Adding to or deleting from a model or database; changing its name, structure (data types or, for object-relational, collection type), check clause, or extended attributes
Code	Deleting from model or database; adding to or changing the name in model; changing the code or its type (stored procedure, function, trigger, etc.)
NOTE  You can also use the Update Database Wizard to update your model based on a database schema, just as you can with the Update Model Wizard. The key difference between these wizards is that only the Update Database Wizard can make changes to a database.


Connecting to a data source when updating

 How To	 Related Topics

As part of updating a model or database, you must specify how to locate and communicate with the database with which you want to work. Whether you are using a desktop or client/server database, or Microsoft Repository, you can use the Update Model Wizard or Update Database Wizard to

•	Select an installed Visio® Enterprise database driver.

•	Associate a 32-bit ODBC (Open Database Connectivity) driver with an installed Visio Enterprise database driver.

•	Select or create a 32-bit ODBC data source. For details about creating a data source, see the documentation for the Microsoft 32-bit ODBC Data Source Administrator.

You can also set up the connections before working with the wizards. If you are updating a database or model stored in Microsoft Repository, you must connect to Microsoft Repository by using the Microsoft DBM Repository driver provided with Visio Enterprise.

When you generate a DDL script that performs the updates, an ODBC data source is not required unless your target database requires server information in its CREATE DATABASE statement. The Update Database Wizard detects whether the database requires an ODBC data source and provides options for you to choose.


Running the Update Model Wizard

 How To	 Related Topics

The Update Model Wizard steps you through the process of connecting Visio® Enterprise to your target database, comparing the model diagram to the actual database’s schema, and then resolving any conflicts. If the wizard detects differences between the model and database, you can choose to update the model or ignore the conflict for now.


To synchronize a database with your model
To synchronize a database with your model:

1	Choose Database > Model > Update.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	On the first screen, do the following:

From the Installed Drivers list, choose the driver. If you have not already associated a Visio Enterprise database driver with a particular 32-bit ODBC data source, click Setup to create the association now.

Under Data Sources, choose the source of the database you are updating, then click Next. If you have not already created a data source for the existing database, click New to create a new one, then follow the instructions on the screens.When you finish creating a new data source, click OK.

Visio Enterprise adds its name to the Data Sources list.

3	In the Connect Data Source dialog box, type a user name and password, then click OK.

4	On the next screen, select an item in the list of conflicts, then click the resolution option you want to use:

No Change Do not change either the model or the database.

Update Model Change the model to match the database schema.

Repeat for each item in the list. When you are finished, click Next.

5	On the next screen, review the changes that the wizard will make, then click Next.

6	On the final screen, click Finish to accept the changes and update the model.

The wizard checks the model for semantic and implementation errors and writes a list of progress notes, errors, and warnings in the Output window. If validation succeeds, the wizard updates the model.

If validation fails, you can correct the errors, then run the Update Model Wizard again. For example, if a table’s primary key has been removed in the database, validation fails.

  Overview


Running the Update Database Wizard

 How To	 Related Topics

The Update Database Wizard walks you step by step through the process of connecting to a database, comparing the model in Visio Enterprise to the target database, and then resolving any differences by updating the model or the database. To update the database, you can choose to generate a DDL script that performs the changes or directly alter the database.

Before you update the database schema, you should

•	Verify that your model is valid by choosing Database > Model > Check. The Update Database Wizard prompts you to check your model if it detects errors.

•	Verify that your system is set up correctly with appropriate access rights to the target desktop or client/server database and all the necessary drivers and client software required by your target database.

For details, see Connecting to a DBMS.

NOTE  Before you run the Update Database Wizard to alter any database, be sure to use your usual safety measures to protect your data.


Creating a script that updates a database

 How To	 Related Topics

With the Update Database Wizard, you can generate a DDL script for updating and physically altering a database based on a database model diagram. When the wizard connects to the target database, it uses the server name, database name, and other information to generate the appropriate DDL (and, if necessary, DML) statements in a text file. The script’s exact contents depend on the target database driver, which tells the wizard the type of script to create, the data types that are supported, and other options specific to the database. You can then review the script, edit it if you want, and execute it later from within your database application.

The Update Database Wizard generates a text file of DDL statements to create, modify, and delete databases objects, such as tables, columns, and indexes, based on your model. To control how the wizard generates comments in the DDL script it creates, choose Database > Drivers, then click the Driver Misc. tab. On this tab you can check options for how the wizard creates the SQL statements in the update script.

TIP  After you generate a DDL script, open it in any text editor to review it for errors before running the script in your database application.


To create an update script
To create an update script:

1	Choose Database > Update.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	On the first screen, do the following:

•	Check Detect Changes In The Database.

•	To write discrepancies to a file, check Log Conflicts, then specify a file name and path.

•	Check Generate A Text File With The DDL script, then specify a file name and path for the script.

On the first screen of the Update Database Wizard, you can choose to create a script to alter your database and create a log file of changes.

Click Next.

3	On the second screen, do the following:

From the Installed Visio Drivers list, choose a driver. For example, if you want to update an existing Oracle database, select the Oracle driver. If you have not already associated a Visio Enterprise driver with a particular 32-bit ODBC data source, click Setup to create the association now.

Choose the Visio Enterprise driver associated with the database you want to update.

Under Data Sources, choose the source of the database you are updating, then click Next. If you have not already created a data source for the existing database, click New to create a new one, then follow the instructions on the screens. When you finish creating a new data source, click OK.

Visio Enterprise adds its name to the Data Sources list.

4	In the Connect Data Source dialog box, type a user name and password, then click OK.

5	On the next screen, select an item in the list of conflicts, then click the resolution option you want to use:

No Change Do not change either the model or the database.

Update Model Change the model to match the database.

Update Database Change the database to match the model.

6	On the next screen, any updates to the model that you specified are listed. Review the changes, then click Next.

For example, if your model contains a table that is not in the database, and you instructed the wizard to update the model, the table will be deleted from the model.

7	On the next screen, any updates to the database schema that you specified are listed. Review the changes, then click Next.

For example, if your model contains a table that is not in the database, and you instructed the wizard to update the database, the table will be added to the database.

8	On the final screen, click Finish to accept the changes and create an update script.

The wizard checks the model for semantic and implementation validity and writes a list of progress notes, errors, and warnings in the Output window.

•	If validation succeeds, the wizard prompts you to save the script in the file you defined on the first screen of the wizard.

•	If validation fails, click Cancel to exit the wizard. For example, if a table’s primary key has been removed in the database, validation fails. Correct the errors before running the Update Database Wizard again.

NOTE  The Update Database Wizard only creates a DDL script; it does not implement it. You must run the script manually from within your database application.

  Overview


Updating a database schema directly

 How To	 Related Topics

Rather than generate a DDL script, the Update Database Wizard can directly connect to a target database and update it, altering existing table definitions and adding and removing tables. You can also update the schema directly and at the same time generate a DDL script.


To update a database schema directly
To update a database schema directly:

1	Choose Database > Update.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	On the first screen, do the following:

•	Check Detect Changes In The Database.

•	To write discrepancies to a file, check Log Conflicts, then specify a file name and path.

•	Check Update The Database.

•	To create a DDL script as well, check Generate a Text File With The DDL Script, then enter a file name.

Click Next.

3	On the second screen, do the following:

From the Installed Visio Drivers list, choose a driver. For example, if you want to update an existing Oracle database, select the Oracle driver. If you have not already associated a Visio Enterprise driver with a particular 32-bit ODBC data source, click Setup to create the association now.

Under Data Sources, choose the source of the database you are updating, then click Next. If you have not already created a data source for the existing database, click New to create a new one, then follow the instructions on the screens. When you finish creating a new data source, click OK.

Visio Enterprise adds its name to the Data Sources list.

4	In the Connect Data Source dialog box, type a user name and password, then click OK.

5	On the next screen, select an item in the list of conflicts, then click the resolution option you want to use:

No Change Do not change either the model or the database.

Update Model Change the model to match the database.

Update Database Change the database to match the model.

Repeat for each item, then click Next.

6	On the next screen, any updates to the model that you specified are listed. Review the changes, then click Next.

For example, if your model contains a table that is not in the database, and you instructed the wizard to update the model, the table will be deleted from the model.

7	On the next screen, any updates to the database schema that you specified are listed. Review the changes, then click Next.

For example, if your model contains a table that is not in the database, and you instruct the wizard to update the database, the table is added to the database.

8	On the final screen, click Finish to accept the changes and update the database schema.

The wizard checks the model for semantic and implementation errors and writes a list of progress notes, errors, and warnings in the Output window.

•	If validation succeeds, the wizard connects to your database and updates it with the changes you specified. A message in the Output window notifies you when the process is finished.

•	If validation fails, click Cancel to exit the wizard. For example, if a table’s primary key has been removed in the database, validation fails. Correct the errors in the model before running the Update Database Wizard again.

  Overview


Next steps

 Related Topics

After you update a model with the Update Model Wizard, your next step is to review and refine the model further, and to check it for errors.

After you update a database with the Update Database Wizard, your next step is to review and test the database. If you are satisfied with the changes, archive your model diagram. Otherwise, refine your model further, and then try updating again.

If you want to	See
Refine a database model and check it for errors	Refining database models
Generate a new database schema after synchronizing or updating	Generating a database schema
Update an existing database schema after updating a model	Updating a database from the model


Creating reports

 Related Topics

You can create customizable reports in Visio® Enterprise that describe your database model by displaying data definitions and formatting information. For example, you can create a report that documents your database design to present to a client. You can create reports that summarize file and database statistics or that document the tables in your model. In addition, if you are creating an object-relational model, you can also report on data types.

This set of topics describes how to use the Report Wizard and the Report window to customize the information you want to appear in a report, and then to preview, print, or export that information.

Topics in this section

Generating reports

Summarizing file and database statistics in a report

Listing database tables in a report

Listing data types in a report

Next steps


Generating reports

 How To	 Related Topics

You can create, print, preview, and export comprehensive reports based on the contents of the current model on the drawing page. Visio® Enterprise includes three types of preformatted reports, which you can customize to include the data definitions and formats that you want. Creating a new report is a two-step process:

1	Use the Report Wizard to select the type of report you want to create.

2	Define and customize the report in the Report window.

The Report Wizard automatically launches the Report window, where you can choose options for refining the report’s contents as well as printing, previewing, and exporting the report.


To create a new report
To create a new report:

1	Choose Database > Report, then click Next.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	Choose the type of report you want to create, then click Finish.

3	In the Report window, define and format the information you want to include in your report.

4	Click Print, Preview, or Export To RTF to generate the report.

5	Click Close to close the Report window.

You can now format report pages and report titles.

  Overview


Formatting report titles

 How To	 Related Topics

On the Headers tab, you can specify a report title, which appears at the top of the first report page, a document title, which appears beneath the report title on the first page, and a group header supplied by Visio® Enterprise that separates groups of records in your report. Each group is listed on the Sort/Group tab.

To automatically generate common header elements, such as page number, time, and date, you can use a formatting code. A formatting code is simply a percent sign (%) followed by a word.

Formatting codes to use in a report’s header text

Code	Description
%Author	Name specified in the Properties dialog box (File > Properties)
%Date	Current system date
%DocTitle	File name of the model diagram
%DocTitleLong	Title of the model diagram, set on the Summary tab in the Properties dialog box (File > Properties)
%File	Path and file name of the model diagram
The following table describes how to work in the Headers tab.

Adding header text

To	Do this
Type header text	On the Headers tab, click in an edit box, then type the text you want.
Insert a formatting code in the header text	On the Headers tab, click in an edit box, click Insert, then choose a code.
Add line breaks to text	Press Ctrl+Enter.
Delete text	Select the text, then press the Delete key.


Formatting report pages

 How To	 Related Topics

To format groups of information in your report visually, use the Pagination tab in the Report window. You can set page breaks or specify to print separators, which are bars that appear between records and groups of records. In a report, record refers to all the data about one object type; that is, entities or tables. A group is a set of records sorted by a key that you specify on the Sort/Group tab.

A	If the report contains more than one page, specifies how to separate each.

B	If you set a Document Separator, specifies to number all pages consecutively (Continuous) or to renumber after the separator with the value set for Starting Page Number.

C	Specifies how to separate groups in the report. Groups are set on the Sort/Group tab.

D	Specifies how to separate all the data about one entity or table in the report.

E	Sets the first page number.

F	Opens the Page Setup dialog box, where you can set print options.

Formatting options on the Pagination tab in the Report window


 Previewing a report

 How To	 Related Topics

To preview a report before you print or export it, click the Preview button in the Report window. Previewing provides a view of the report so that you can verify its contents. To change the report’s contents, click Close to return to the tabs in the Report window.

The following table describes how to change the view of your report as you work in the preview window.

Changing the view of a report

To	Click
Navigate between pages	Next Page or Prev Page
Change the magnification of the view	Zoom In or Zoom Out
Change the number of pages being previewed	One Page or Two Page


 Exporting a report

 How To	 Related Topics

To reuse the contents of a report in another application, or to customize its contents further, you can export the report as a Rich Text Format (RTF) file. Visio® Enterprise exports graphic elements in a report as bitmaps and exports example data in a tabular format as part of the report. You can then open and edit the RTF file in any application that supports RTF, such as Microsoft Word.


To export a report
To export a report:

1	In the Report window, click Export To RTF.

2	In the Export To RTF dialog box, type a file name and path, then click Save.

3	Click Close to close the Report window.

Visio® Enterprise exports the report as an RTF file and displays a progress indicator in the status bar.

4	Click Close to close the Report window.

  Overview


Summarizing file and database statistics in a report

 How To	 Related Topics

When you want a high-level summary of the contents of a database model diagram, use the Report Wizard to create a statistical report. In a statistical report, Visio® Enterprise tallies the number and type of constraints, foreign keys, and attributes, and summarizes source file information for your model.

When you create a statistical report, you can choose to describe your model using entity relationship terms, such as entities, attributes, and relationships, or object-relational terms, such as tables, columns, and keys. You can set this option on the Preferences tab in the Report window.


To create a statistical report
To create a statistical report:

1	Choose Database > Report, then click Next.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	In the Report Wizard, choose Statistical Report, then click Finish.

The Report window opens.

3	On the Report window tabs, do the following:

Preferences tab Choose the notation you want to use in the report.

Headers tab Create a title for the report.

Pagination tab Set page breaks and other formatting options.

4	When you are finished, click Preview to see your report. Or click Print or Export To RTF. Click Close to close the Report window.

  Overview


Listing database tables in a report

 How To	 Related Topics

With the Report Wizard, you can create a table report that shows how objects in your model, such as tables and columns, will appear in the database you generate or update from the model. Visio® Enterprise includes two types of table reports.

•	A conceptual table report, which describes your model using the conceptual names of objects specified in the various Properties dialog boxes.

•	A physical table report, which describes the model using the physical names of objects specified in the various Properties dialog boxes.

To choose the type of report you want, use the Preferences tab in the Report window, where you can also choose whether to describe the model using entity relationship or object-relational terminology.

A table report can also describe the attributes associated with the tables, columns, indexes, and foreign keys in your model. On the Attributes tab in the Report window, you can choose from a number of attributes in each of these categories to include in your report. If you don’t want to report on a particular category, such as Foreign Key, uncheck all the attributes associated with that category.

You can sort the information that appears in your report using the Sort/Group tab in the Report window.


To create a table report
To create a table report:

1	Choose Database > Report, then click Next.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	In the Report Wizard, choose Table Report, then click Finish.

The Report window opens.

3	On the Report window tabs, do the following:

Preferences tab Choose the type of report you want, conceptual or physical, and the type of notation to use.

Attributes tab Choose the database, table, column, index, and foreign key attributes you want in your report.

Sort/Group tab Choose the report objects that will be used to sort the report contents.

Headers tab Create a title for the report.

Pagination tab Set page breaks and other formatting options.

4	When you are finished, click Preview to see your report. Or click Print or Export To RTF. Click Close to close the Report window.

  Overview


Listing data types in a report

 How To	 Related Topics

You can create a report of the composite, user-defined, and built-in types represented in your database model diagram. With the Report Wizard, you can create a types report, which displays data types in your report. Then in the Report window, you can tailor your report by choosing the data types to include.

Your types report can also include attribute information, such as type names, base types, and derived types. Choose from the options on the Attributes tab in the Report window to indicate which attributes to include. Your report is sorted by the class of data type and then by type name. To change the sorting criteria, use the options on the Sort/Group tab.


To create a types report
To create a types report:

1	Choose Database > Report, then click Next.

NOTE  This command is available only when you start a model in Visio® Enterprise using the Database Model template.

2	In the Report wizard, choose Types Report, then click Finish.

The Report window opens.

3	On the Report window tabs, do the following:

Selection tab Choose the types you want to show in your report.

Attributes tab Choose the attributes you want in your report.

Sort/Group tab Choose the report objects that will be used to sort the report contents. Only the options you checked on the Attributes tab appear on this tab as records you can sort or group.

Headers tab Create a report title.

Pagination tab Set page breaks and other formatting options.

4	When you are finished, click Preview to see your report. Or click Print or Export To RTF. Click Close to close the Report window.

  Overview


Next steps

 Related Topics

You can create reports at any time during the modeling process to communicate with clients, coordinate your efforts with those of coworkers, or simply to provide another view of your model’s contents. Depending on where you are in the modeling or reverse engineering process, your next step after creating a report could be to refine your model further, check it for errors, or synchronize the model with the target database.

If you want to	See
Refine your model and check it for errors	Refining database models
Synchronize your model with the target database	Updating a model from a database
Update an existing database schema based on your model	Updating a database from the model


Showing or hiding annotations for Entity shapes

 Related Topics

You can show or hide an Entity shape’s column annotations. Column annotations include Primary key (PK), Foreign Key (FKn), Alternate Keys (Akn), Unique (Un), and Index(In), where n represents multi-part keys or indexes.

Column annotations can be displayed in either Relational or IDEF1X notation.

•	In Relational notation, column annotations are displayed in the left column.

•	In IDEF1X notation, column annotations are surrounded by parentheses and appended to the column name.

To show or hide annotations for all Entity shapes in the document:

1	Choose Database > Document Options.

2	Click the Table tab. For Display, check Annotations, then click OK.

To show or hide annotations for a single Entity shape:

•	If Annotations is unchecked in the Database Document Options dialog box, right-click the Entity shape, then check Annotations to make annotations visible for that Entity shape.

TIP  To reset default document options for the shape, right-click the shape, then uncheck Override Document Options.


Showing or hiding data types for shapes

 Related Topics

You can show or hide data types for Entity, Type, and View shapes.

To show or hide data types for a single shape:

•	Right-click an Entity, Type, or View shape, then check Datatypes.

TIP  To reset default document options for the shape, right-click the shape, then uncheck Override Document Options.


Switching between IDEF1X or Relational notation

 Related Topics

You can switch your entire document or a single shape to IDEF1X or Relational notation.

To switch between IDEF1X or Relational notation for the document:

1	Choose Database > Document Options.

2	Click the General tab. For Symbol Set, choose IDEF1X or Relational, then click OK.

To switch between IDEF1X or Relational notation for a single shape:

•	Right-click an Entity or View shape, then check IDEF1X Notation or Relational Notation.

TIP  To reset default document options for the shape, right-click the shape, then uncheck Override Document Options.


Showing or hiding the horizontal line for Entity shapes

 Related Topics

For some tables, a horizontal line separating the primary key and columns can make the table easier to read. You can show or hide the horizontal line.

To show or hide the horizontal line for all Entity shapes in the document:

1	Choose Database > Document Options.

2	Click the Table tab. For Order, choose Primary Keys At Top, check Draw Separator Line, then click OK.

To show or hide the horizontal line for a single Entity shape:

1	Right-click an Entity shape, then check Primary Key Section to display the primary keys.

2	Right-click the Entity shape, then check PK Separator Line.

TIP  To reset default document options for the shape, right-click the shape, then uncheck Override Document Options.


Showing or hiding the primary key for Entity shapes

 Related Topics

You can show or hide a table’s primary key.

To show or hide the primary key for all Entity shapes in the document:

1	Choose Database > Document Options.

2	Click the Table tab. For Order, choose Primary Keys At Top, then click OK.

To show or hide the primary key for a single Entity shape:

•	Right-click an Entity shape, then check Primary Key Section.

TIP  To reset default document options for the shape, right-click the shape, then uncheck Override Document Options.


Showing or hiding vertical lines for shapes

 Related Topics

For some shapes, a vertical line separating the columns and data types can make the shape easier to read. You can show or hide the vertical line.

To show or hide vertical lines for a single shape:

1	Right-click the Entity, Type, or View shape, then check Datatypes.

2	Right-click an Entity, Type, or View shape, then check Vertical Line(s).

TIP  To reset default document options for the shape, right-click the shape, then uncheck Override Document Options.